Excel Financial Formulas A Practical Guide

Microsoft Excel is a powerhouse for financial analysis. Whether you are evaluating investments, measuring performance, or projecting cashflows, Excel offers a rich set of builtin functions that simplify complex calculations. This page walks through the most useful financial formulas, shows how they work, and provides realworld examples you can copy straight into your workbook.

1. Time Value of Money Functions

1.1 PV Present Value

The PV function returns the present value of a series of future cash flows, given a constant interest rate.

PV(rate, nper, pmt, [fv], [type])

• rate interest rate per period (e.g., 5%/12 for a monthly rate).
• nper total number of periods.
• pmt payment each period (use 0 if there is no regular payment).
• fv future value (optional, default 0).
• type when payments are made (0 = end, 1 = beginning; optional).

Example: A loan of $10,000 at 6% annual interest, paid monthly over 5 years.

=PV(6%/12, 5*12, -200)

Result: -$11,753.45. The negative sign indicates a cash outflow.

1.2 FV Future Value

FV(rate, nper, pmt, [pv], [type])

Calculates the value of an investment after a series of equal payments.

=FV(8%/12, 10*12, -150, -2000)

Result: $30,721.11 the amount after ten years with an initial deposit of $2,000 and monthly contributions of $150.

1.3 NPV Net Present Value

NPV(rate, value1, [value2], )

Discounts a series of cash flows to the present and adds them together. Note that the first cash flow (often the initial investment) must be added separately.

=-5000 + NPV(10%, 1500, 2000, 2500, 3000)

Result: $3,260.41 a positive NPV suggests the project is worthwhile at a 10% discount rate.

2. Rate of Return Functions

2.1 IRR Internal Rate of Return

IRR(values, [guess])

Finds the discount rate that makes the NPV of cash flows equal to zero.

=IRR(A2:A7)

Assuming A2:A7 contains -5000, 1500, 2000, 2500, 3000, 3500, the IRR is 14.87%.

2.2 XIRR IRR for Irregular Cash Flows

XIRR(values, dates, [guess])

Use when cash flow dates are not evenly spaced.

=XIRR(B2:B7, C2:C7)

Here B holds cash amounts and C holds the corresponding dates.

3. Loan and Amortization Functions

3.1 PMT Payment

PMT(rate, nper, pv, [fv], [type])

Calculates the periodic payment for a loan or investment.

=PMT(4%/12, 30*12, 250000)

Result: -$1,193.54 per month for a 30year mortgage of $250,000 at 4% annual interest.

3.2 IPMT Interest Portion of a Payment

IPMT(rate, per, nper, pv, [fv], [type])

Finds the interest paid in a specific period.

=IPMT(5%/12, 1, 60, 10000)

First month interest: $41.67.

3.3 PPMT Principal Portion of a Payment

PPMT(rate, per, nper, pv, [fv], [type])

Calculates the principal component of a specific payment.

=PPMT(5%/12, 1, 60, 10000)

First month principal: -$8.33 (negative sign indicates cash outflow).

4. Investment Analysis Functions

4.1 RATE Interest Rate

RATE(nper, pmt, pv, [fv], [type], [guess])

Derives the interest rate for a loan or investment given the payment schedule.

=RATE(24, -150, 3000)

Monthly rate 0.0045 annualized 5.57%.

4.2 MIRR Modified Internal Rate of Return

MIRR(values, finance_rate, reinvest_rate)

Accounts for a different cost of financing and reinvestment rate.

=MIRR(A2:A7, 8%, 10%)

MIRR 12.31%.

5. Depreciation Functions

5.1 SLN StraightLine Depreciation

SLN(cost, salvage, life)

Annual depreciation expense.

=SLN(50000, 5000, 5)

Result: $9,000 per year.

5.2 DB Declining Balance

DB(cost, salvage, life, period, [month])

Depreciation for a given period using the doubledeclining method.

=DB(50000, 5000, 5, 1)

Firstyear depreciation: $20,000.

6. Annuity Functions

6.1 NPER Number of Periods

NPER(rate, pmt, pv, [fv], [type])

Determines how many periods are needed to reach a target.

=NPER(7%/12, -200, 0, 10000)

44.7 months to accumulate $10,000.

7. Practical Example: Building a Simple Investment Model

Below is a minimodel that combines several functions to show how an investment grows, when a loan is repaid, and what the overall return looks like.

Year	Cash Inflow	Cash Outflow (Loan Payment)	Net Cash Flow	Discount Factor (10%)	PV of Net Cash Flow
0	$0	$0	$0	1.00	$0
1	$5,000	=$PMT(5%/12,30*12,-200000)	=B2-C2	=1/(1+10%)^1	=E2*D2
2	$5,500	=C2	=B3-C3	=1/(1+10%)^2	=E3*D3
3	$6,050	=C2	=B4-C4	=1/(1+10%)^3	=E4*D4
10	$8,000	=C2	=B11-C11	=1/(1+10%)^10	=E11*D11

To calculate the overall NPV, sum the PV of Net Cash Flow column and add the initial investment (if any).

=SUM(F2:F11) - InitialInvestment

8. Tips for Using Financial Functions Effectively

• Keep sign conventions consistent. Payments (outflows) are usually negative, receipts (inflows) positive.
• Use cell references. Hardcoding numbers makes formulas brittle.
• Check the type argument. For annuities paid at the beginning of the period, set type=1.
• Combine with data tables. Scenario analysis (e.g., varying interest rates) is simple with Data WhatIf Analysis Data Table.
• Document assumptions. Add comments or a separate Assumptions sheet to keep track of rates, periods, and cashflow timing.

9. Further Learning Resources

Microsofts official support site, Excels builtin Function Wizard, and community sites such as MrExcel or ExcelJet provide indepth tutorials and examples.

For a more structured study, consider books such as Financial Modeling in Excel by Danielle Stein Fairhurst, or online courses on platforms like Coursera and LinkedIn Learning.

With these formulas in your toolbox, you can handle everything from simple loan calculations to complex investment appraisals, all within the familiar environment of Excel.