Finance formulas
Loan payments, interest, savings growth, NPV and IRR, CAGR, depreciation and break-even. 30 answers, each checked by running it, with a live example and an exercise.
How to calculate a loan payment in Excel
=PMT(B3/12,B4*12,-B2)Use PMT with the monthly rate, the number of months and the amount:
=PMT(B3/12,B4*12,-B2)returns the monthly payment.How to calculate a monthly mortgage payment in Excel
=PMT(B4/12,B5*12,-B2*(1-B3))Take the down payment off the price and use PMT:
=PMT(B4/12,B5*12,-B2*(1-B3)).How to calculate total interest paid on a loan in Excel
=PMT(B3/12,B4*12,-B2)*B4*12-B2Multiply the payment by the number of payments and subtract the amount borrowed:
=PMT(B3/12,B4*12,-B2)*B4*12-B2.How to calculate how many months to pay off a loan in Excel
=ROUNDUP(NPER(B3/12,-B4,B2),0)Use NPER with the monthly rate, the payment and the balance, and round up:
=ROUNDUP(NPER(B3/12,-B4,B2),0).How to calculate the interest rate on a loan in Excel
=RATE(B4,-B3,B2)*12Use RATE with the number of payments, the payment and the amount, then multiply by 12:
=RATE(B4,-B3,B2)*12.How to calculate the future value of savings in Excel
=FV(B4/12,B5*12,-B3,-B2)Use FV with the monthly rate, the number of months, the monthly deposit and the starting balance:
=FV(B4/12,B5*12,-B3,-B2).How to calculate present value in Excel
=PV(B4,B3,0,-B2)Use PV with the rate, the years and the future amount:
=PV(B4,B3,0,-B2)is what that amount is worth today.How to work out how much you can borrow in Excel
=PV(B3/12,B4*12,-B2)Use PV with the payment you can afford:
=PV(B3/12,B4*12,-B2)is the largest loan that payment pays off.How to calculate NPV in Excel
=NPV(E1,B3:B7)+B2Discount the future cash flows with NPV and add the upfront amount separately:
=NPV(E1,B3:B7)+B2.How to calculate IRR in Excel
=IRR(B2:B7)Give IRR the cash flows in order, starting with the negative investment:
=IRR(B2:B7).How to calculate CAGR in Excel
=(B6/B2)^(1/(A6-A2))-1Divide the end value by the start value, raise it to 1 over the number of years and subtract 1:
=(B6/B2)^(1/(A6-A2))-1.How to calculate compound interest in Excel
=B2*(1+B3/B4)^(B4*B5)Use principal × (1 + rate/n)^(n × years):
=B2*(1+B3/B4)^(B4*B5), with n the number of compounding periods per year.How to calculate simple interest in Excel
=B2*B3*B4Multiply principal by rate by time in years:
=B2*B3*B4.How to calculate straight-line depreciation in Excel
=(B2-B3)/B4Divide the cost minus the salvage value by the useful life:
=(B2-B3)/B4, the same result as Excel's SLN function.How to calculate the break-even point in Excel
=ROUNDUP(B2/(B3-B4),0)Divide fixed costs by the margin on each unit and round up:
=ROUNDUP(B2/(B3-B4),0)is the number of units you must sell.How to calculate ROI in Excel
=(C2-B2)/B2Subtract the cost from the return and divide by the cost:
=(C2-B2)/B2, formatted as a percentage.How to calculate the payback period in Excel
=LET(n,COUNTIF(C2:C7,"<0"),n-1-INDEX(C2:C7,n)/INDEX(B2:B7,n+1))Count the years still negative, then add the fraction of the next year needed:
=LET(n,COUNTIF(C2:C7,"<0"),n-1-INDEX(C2:C7,n)/INDEX(B2:B7,n+1)).How to convert an annual interest rate to a monthly rate in Excel
=B2/12For a quoted annual rate (APR), divide by 12:
=B2/12. For an effective annual rate, take the 12th root:=(1+B2)^(1/12)-1.How to convert APR to APY in Excel
=(1+B2/B3)^B3-1Compound the periodic rate for a year:
=(1+B2/B3)^B3-1, with the APR in B2 and the compounding periods per year in B3.How to calculate the remaining balance on a loan in Excel
=-FV(B3/12,B5,PMT(B3/12,B4*12,B2),B2)Use FV with the payment from PMT:
=-FV(B3/12,B5,PMT(B3/12,B4*12,B2),B2)is what is still owed afterB5payments.How many months do extra payments save on a loan in Excel
=NPER(B3/12,-B4,B2)-NPER(B3/12,-(B4+B5),B2)Compare NPER with and without the extra amount:
=NPER(B3/12,-B4,B2)-NPER(B3/12,-(B4+B5),B2).How much to save each month to reach a goal in Excel
=-PMT(B4/12,B3*12,-B5,B2)Use PMT with the goal as the future value:
=-PMT(B4/12,B3*12,-B5,B2)is the monthly amount, after what you have already saved.How long will my savings last in Excel
=NPER(B4/12,B3,-B2)/12Use NPER with the withdrawal as a payment you receive:
=NPER(B4/12,B3,-B2)/12gives the number of years.How to adjust a price for inflation in Excel
=B2*(1+B3)^B4Grow the price by the inflation rate for each year:
=B2*(1+B3)^B4is what it will cost inB4years.How long does it take to double money in Excel
=NPER(B2,0,-1,2)Use NPER with a present value of -1 and a future value of 2:
=NPER(B2,0,-1,2)gives the years to double.How to calculate annualized return from monthly returns in Excel
=PRODUCT(1+B2:B10)^(12/COUNT(B2:B10))-1Compound the monthly returns and scale to 12 months:
=PRODUCT(1+B2:B10)^(12/COUNT(B2:B10))-1.How to calculate the price of a bond in Excel
=PV(B4/B6,B5*B6,-B2*B3/B6,-B2)Use PV with the yield per period, the number of coupons, the coupon payment and the face value:
=PV(B4/B6,B5*B6,-B2*B3/B6,-B2).How to calculate budget vs actual variance in Excel
=C2-B2Subtract the budget from the actual:
=C2-B2. Divide by the budget for the variance as a percentage.How to calculate a weighted average interest rate in Excel
=SUMPRODUCT(B2:B5,C2:C5)/SUM(B2:B5)Weight each rate by its balance:
=SUMPRODUCT(B2:B5,C2:C5)/SUM(B2:B5).How to calculate WACC in Excel
=B2/(B2+B3)*B4+B3/(B2+B3)*B5*(1-B6)Weight the cost of equity and the after-tax cost of debt by their shares of the total:
=B2/(B2+B3)*B4+B3/(B2+B3)*B5*(1-B6).