Excel Financial Functions: Loans, Investments, and More

Excel financial functions solve for one part of a cash-flow model when the other assumptions are known. You can calculate a loan payment, estimate the future value of regular deposits, separate principal from interest, evaluate a project, or calculate depreciation.

The function is only half the model. The timing of each cash flow, the rate per period, payment frequency, sign convention, and omitted costs all affect the result. This guide makes those assumptions explicit and uses original, independently checked examples.

Excel performs the calculation you specify; it does not decide whether an interest rate, cash-flow forecast, depreciation method, or investment assumption is appropriate. Verify material financial decisions against the relevant contract, accounting policy, tax rules, and qualified advice.

Choose the financial function by the unknown value

Microsoft’s financial functions reference lists additional functions for securities, coupon dates, yields, cumulative loan amounts, and specialized depreciation methods. The functions above cover the most common loan, savings, project, and asset-modeling tasks.

Set the timing and signs before writing a formula

Most mistakes come from inconsistent assumptions rather than misspelled function names. Work through these checks first:

1. Choose one period. For monthly cash flows, use a monthly rate and a monthly number of periods. For annual cash flows, use annual values for both.
2. Use one point of view. Money received and money paid must have opposite signs. From a borrower’s perspective, the loan proceeds are positive and repayments are negative.
3. Set payment timing. In annuity functions, type is 0 or omitted for end-of-period payments and 1 for beginning-of-period payments.
4. Confirm the model is constant where required. PMT, PV, FV, NPER, and RATE assume a constant rate and constant periodic payments.
5. Keep full precision. Format a result for display, but let downstream formulas use the unrounded value unless the agreement itself requires rounded cash flows.

The examples use commas between arguments. Excel may require semicolons under some regional settings. Follow the separator shown by Formula AutoComplete.

Calculate a loan payment with PMT

Suppose a loan has a principal of 280000, a nominal annual rate of 5.4%, a 30-year term, monthly payments, and a zero balance after the final payment. For end-of-month payments, use:

=-PMT(5.4%/12,30*12,280000,0,0)

Use the PMT function to calculate the monthly payment for a 30-year loan with a 5.4% annual interest rate.

The result is 1572.29 when rounded to cents. The annual rate is divided by 12 to create a monthly rate, and the 30-year term is multiplied by 12 to create 360 monthly periods.

Excel’s cash-flow convention would ordinarily return the payment as a negative number because the positive loan proceeds and payments move in opposite directions. The leading minus sign flips the displayed result to positive; it does not change the underlying economics.

The PMT documentation notes that the calculated payment includes principal and interest but not taxes, reserve payments, or loan fees. A mortgage model may therefore need separate rows for insurance, taxes, association charges, or other costs.

Solve the same annuity model for PV, NPER, or RATE

The core annuity functions use the same variables in different orders. Choose the function that represents the missing value.

PV returns the amount today that is equivalent to the specified series of future payments.

Use the PV function to calculate how much principal a fixed monthly payment can support over a 30-year term.

NPER returns a mathematical number of periods; a real payoff schedule may require one final, smaller payment when the result is fractional.

Use the NPER function to calculate how many monthly payments are needed to repay a loan.

RATE returns the rate for one payment period, so multiplying a monthly result by 12 produces a nominal annual rate.

Use the RATE function to calculate the periodic interest rate, then multiply by 12 to return a nominal annual rate.

To convert that monthly RATE result into an effective annual rate instead, use:

=(1+RATE(48,-455,20000))^12-1

The result is approximately 4.47%. Microsoft’s references for PV and NPER document the shared annuity arguments. RATE is iterative and can return #NUM! when it cannot converge; changing an unsupported model into a solvable one requires revisiting the inputs, not merely hiding the error.

Project regular savings with FV

Assume an account starts with 10000, receives 300 at the end of every month, earns a constant nominal annual rate of 6% compounded monthly, and runs for 10 years:

=FV(6%/12,10*12,-300,-10000,0)

The future value is 67357.77. The opening deposit and monthly contributions are negative because they are cash paid into the account; the ending balance is returned as positive. Changing the final type argument to 1 would model deposits at the beginning of each month.

Use the FV function to calculate how a starting balance and regular monthly deposits can grow over time.

FV assumes the rate and periodic contribution remain constant. It does not model variable market returns, fees, taxes, or missed deposits unless you build those items into a separate cash-flow schedule. See Microsoft’s FV reference for the full argument definitions.

Split a payment into interest and principal

For the 280000 loan, the first monthly payment can be separated with IPMT and PPMT:

=-IPMT(5.4%/12,1,30*12,280000)
=-PPMT(5.4%/12,1,30*12,280000)

The first formula returns 1260.00 of interest. The second returns 312.29 of principal. Together they equal the 1572.29 payment, apart from any display rounding.

Use IPMT and PPMT to separate a loan payment into its interest and principal components.

The per argument identifies the payment period and must fall between 1 and nper. As a fixed-rate, fully amortizing loan progresses, its interest portion decreases and its principal portion increases. The formulas still need the same rate, term, present value, future value, and payment timing as the original PMT calculation. Microsoft documents these arguments in the IPMT and PPMT references.

Evaluate regular project cash flows with NPV and IRR

Suppose a project has one immediate investment followed by four annual cash inflows:

If the cash flows are stored in B2:B6 and the discount rate is 9%, calculate net present value with:

=NPV(9%,B3:B6)+B2

The result is 2856.48. Excel’s NPV function treats each supplied value as occurring at the end of a period, so the immediate investment in B2 is added separately rather than included inside NPV.

Use the NPV function to evaluate a project’s future cash flows, then add the time-zero investment separately.

For the internal rate of return, include the entire sequence:

=IRR(B2:B6)

The result is approximately 11.62% per annual period. IRR requires at least one negative and one positive cash flow. It solves iteratively, and some cash-flow patterns can have more than one possible result or no result. A different optional guess can lead Excel to a different valid solution.

The Microsoft guide to NPV and IRR explains these timing rules and the iterative behavior. A positive NPV or an IRR above a chosen hurdle rate is a model output, not proof that the forecasts or discount rate are reliable.

Use XNPV and XIRR for actual dates

NPV and IRR assume equally spaced periods. When cash flows occur on actual, irregular dates, store valid Excel dates beside the amounts and use the date-aware functions.

With dates in A2:A6 and amounts in B2:B6, use:

=XNPV(9%,B2:B6,A2:A6)
=XIRR(B2:B6,A2:A6)

For these dates, XNPV returns approximately 5628.56, while XIRR returns approximately 15.80%. The result differs from the regular-period example because the cash inflows arrive on a different schedule.

Use XNPV when cash flows occur on actual irregular dates rather than at equal intervals.

Enter real Excel dates rather than text that merely looks like a date, and keep each date aligned with its cash flow. The first date establishes the starting point for the calculation. Like IRR, XIRR is iterative and may return #NUM! when it cannot find a solution.

Compare nominal and effective annual rates

A nominal annual rate does not by itself show the effect of intra-year compounding. For a nominal rate of 6% compounded monthly, use:

=EFFECT(6%,12)

The effective annual rate is approximately 6.1678%. NOMINAL performs the reverse conversion when you know the effective annual rate and the number of compounding periods. Microsoft’s EFFECT reference defines the two required arguments.

This conversion is different from changing a monthly RATE result into an annual rate. Always label whether a rate is periodic, nominal annual, or effective annual so readers do not compare unlike measures.

Calculate straight-line or declining depreciation

Assume equipment costs 60000, has a salvage value of 6000, and a useful life of five years. Straight-line depreciation is:

=SLN(60000,6000,5)

The result is 10800 per year. SLN spreads the depreciable amount—cost minus salvage value—evenly across the useful life.

For double-declining balance depreciation in the first year, use:

=DDB(60000,6000,5,1)

The first-year depreciation is 24000. With the same inputs and period 2, the result is 14400. DDB uses an accelerated method, so depreciation is higher in the early periods and decreases later. Its optional factor defaults to 2.

Other methods include DB for fixed-declining balance, SYD for sum-of-years’ digits, and VDB for declining balance over a specified interval. The correct method and useful life depend on the applicable reporting or tax framework; Excel does not select them. See the official references for SLN and DDB.

Troubleshoot financial formula errors

Frequently asked questions

Build the model around the cash-flow timeline

Start with the timeline, not the function name. Mark when money changes hands, choose the matching period, assign signs from one consistent point of view, and state whether payments occur at the beginning or end. Then choose the function that solves the one unknown value.

After the formula works, test it against a zero-rate case, a shorter term, a different payment time, and one independently calculated example. That discipline catches errors a plausible-looking currency or percentage result can easily hide.