# Finance formulas in Excel and Google Sheets

> Loan payments, interest, savings growth, NPV and IRR, CAGR, depreciation and break-even. 30 answers, each checked by running it.

Source: https://spreadsheetpractice.com/formulas/topic/finance · Updated October 29, 2026 · Formulas and results on this page were run by the Spreadsheet Practice formula engine on October 30, 2026

## Answers

- [How to calculate a loan payment in Excel](https://spreadsheetpractice.com/formulas/calculate-a-loan-payment): Use PMT with the monthly rate, the number of months and the amount: =PMT(B3/12,B4*12,-B2) returns the monthly payment. `=PMT(B3/12,B4*12,-B2)`
- [How to calculate a monthly mortgage payment in Excel](https://spreadsheetpractice.com/formulas/calculate-a-monthly-mortgage-payment): Take the down payment off the price and use PMT: =PMT(B4/12,B5*12,-B2*(1-B3)). `=PMT(B4/12,B5*12,-B2*(1-B3))`
- [How to calculate total interest paid on a loan in Excel](https://spreadsheetpractice.com/formulas/calculate-total-interest-paid-on-a-loan): Multiply the payment by the number of payments and subtract the amount borrowed: =PMT(B3/12,B4*12,-B2)*B4*12-B2. `=PMT(B3/12,B4*12,-B2)*B4*12-B2`
- [How to calculate how many months to pay off a loan in Excel](https://spreadsheetpractice.com/formulas/months-to-pay-off-a-loan): Use NPER with the monthly rate, the payment and the balance, and round up: =ROUNDUP(NPER(B3/12,-B4,B2),0). `=ROUNDUP(NPER(B3/12,-B4,B2),0)`
- [How to calculate the interest rate on a loan in Excel](https://spreadsheetpractice.com/formulas/calculate-the-interest-rate-on-a-loan): Use RATE with the number of payments, the payment and the amount, then multiply by 12: =RATE(B4,-B3,B2)*12. `=RATE(B4,-B3,B2)*12`
- [How to calculate the future value of savings in Excel](https://spreadsheetpractice.com/formulas/future-value-of-savings): Use FV with the monthly rate, the number of months, the monthly deposit and the starting balance: =FV(B4/12,B5*12,-B3,-B2). `=FV(B4/12,B5*12,-B3,-B2)`
- [How to calculate present value in Excel](https://spreadsheetpractice.com/formulas/calculate-present-value): Use PV with the rate, the years and the future amount: =PV(B4,B3,0,-B2) is what that amount is worth today. `=PV(B4,B3,0,-B2)`
- [How to work out how much you can borrow in Excel](https://spreadsheetpractice.com/formulas/how-much-you-can-borrow): Use PV with the payment you can afford: =PV(B3/12,B4*12,-B2) is the largest loan that payment pays off. `=PV(B3/12,B4*12,-B2)`
- [How to calculate NPV in Excel](https://spreadsheetpractice.com/formulas/calculate-npv): Discount the future cash flows with NPV and add the upfront amount separately: =NPV(E1,B3:B7)+B2. `=NPV(E1,B3:B7)+B2`
- [How to calculate IRR in Excel](https://spreadsheetpractice.com/formulas/calculate-irr): Give IRR the cash flows in order, starting with the negative investment: =IRR(B2:B7). `=IRR(B2:B7)`
- [How to calculate CAGR in Excel](https://spreadsheetpractice.com/formulas/calculate-cagr): Divide 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. `=(B6/B2)^(1/(A6-A2))-1`
- [How to calculate compound interest in Excel](https://spreadsheetpractice.com/formulas/calculate-compound-interest): Use principal × (1 + rate/n)^(n × years): =B2*(1+B3/B4)^(B4*B5), with n the number of compounding periods per year. `=B2*(1+B3/B4)^(B4*B5)`
- [How to calculate simple interest in Excel](https://spreadsheetpractice.com/formulas/calculate-simple-interest): Multiply principal by rate by time in years: =B2*B3*B4. `=B2*B3*B4`
- [How to calculate straight-line depreciation in Excel](https://spreadsheetpractice.com/formulas/straight-line-depreciation): Divide the cost minus the salvage value by the useful life: =(B2-B3)/B4, the same result as Excel's SLN function. `=(B2-B3)/B4`
- [How to calculate the break-even point in Excel](https://spreadsheetpractice.com/formulas/calculate-the-break-even-point): 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. `=ROUNDUP(B2/(B3-B4),0)`
- [How to calculate ROI in Excel](https://spreadsheetpractice.com/formulas/calculate-roi): Subtract the cost from the return and divide by the cost: =(C2-B2)/B2, formatted as a percentage. `=(C2-B2)/B2`
- [How to calculate the payback period in Excel](https://spreadsheetpractice.com/formulas/calculate-the-payback-period): 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)). `=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](https://spreadsheetpractice.com/formulas/convert-an-annual-interest-rate-to-monthly): For a quoted annual rate (APR), divide by 12: =B2/12. For an effective annual rate, take the 12th root: =(1+B2)^(1/12)-1. `=B2/12`
- [How to convert APR to APY in Excel](https://spreadsheetpractice.com/formulas/convert-apr-to-apy): Compound the periodic rate for a year: =(1+B2/B3)^B3-1, with the APR in B2 and the compounding periods per year in B3. `=(1+B2/B3)^B3-1`
- [How to calculate the remaining balance on a loan in Excel](https://spreadsheetpractice.com/formulas/remaining-balance-on-a-loan): Use FV with the payment from PMT: =-FV(B3/12,B5,PMT(B3/12,B4*12,B2),B2) is what is still owed after B5 payments. `=-FV(B3/12,B5,PMT(B3/12,B4*12,B2),B2)`
- [How many months do extra payments save on a loan in Excel](https://spreadsheetpractice.com/formulas/months-saved-by-extra-payments): Compare NPER with and without the extra amount: =NPER(B3/12,-B4,B2)-NPER(B3/12,-(B4+B5),B2). `=NPER(B3/12,-B4,B2)-NPER(B3/12,-(B4+B5),B2)`
- [How much to save each month to reach a goal in Excel](https://spreadsheetpractice.com/formulas/monthly-savings-to-reach-a-goal): 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. `=-PMT(B4/12,B3*12,-B5,B2)`
- [How long will my savings last in Excel](https://spreadsheetpractice.com/formulas/how-long-will-savings-last): Use NPER with the withdrawal as a payment you receive: =NPER(B4/12,B3,-B2)/12 gives the number of years. `=NPER(B4/12,B3,-B2)/12`
- [How to adjust a price for inflation in Excel](https://spreadsheetpractice.com/formulas/adjust-a-price-for-inflation): Grow the price by the inflation rate for each year: =B2*(1+B3)^B4 is what it will cost in B4 years. `=B2*(1+B3)^B4`
- [How long does it take to double money in Excel](https://spreadsheetpractice.com/formulas/how-long-to-double-money): Use NPER with a present value of -1 and a future value of 2: =NPER(B2,0,-1,2) gives the years to double. `=NPER(B2,0,-1,2)`
- [How to calculate annualized return from monthly returns in Excel](https://spreadsheetpractice.com/formulas/annualized-return-from-monthly-returns): Compound the monthly returns and scale to 12 months: =PRODUCT(1+B2:B10)^(12/COUNT(B2:B10))-1. `=PRODUCT(1+B2:B10)^(12/COUNT(B2:B10))-1`
- [How to calculate the price of a bond in Excel](https://spreadsheetpractice.com/formulas/calculate-the-price-of-a-bond): 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). `=PV(B4/B6,B5*B6,-B2*B3/B6,-B2)`
- [How to calculate budget vs actual variance in Excel](https://spreadsheetpractice.com/formulas/budget-vs-actual-variance): Subtract the budget from the actual: =C2-B2. Divide by the budget for the variance as a percentage. `=C2-B2`
- [How to calculate a weighted average interest rate in Excel](https://spreadsheetpractice.com/formulas/weighted-average-interest-rate): Weight each rate by its balance: =SUMPRODUCT(B2:B5,C2:C5)/SUM(B2:B5). `=SUMPRODUCT(B2:B5,C2:C5)/SUM(B2:B5)`
- [How to calculate WACC in Excel](https://spreadsheetpractice.com/formulas/calculate-wacc): 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). `=B2/(B2+B3)*B4+B3/(B2+B3)*B5*(1-B6)`

More topics: [Counting formulas](https://spreadsheetpractice.com/formulas/topic/count) · [Sum formulas](https://spreadsheetpractice.com/formulas/topic/sum) · [Lookup formulas](https://spreadsheetpractice.com/formulas/topic/lookup) · [IF and logic formulas](https://spreadsheetpractice.com/formulas/topic/logic) · [Text formulas](https://spreadsheetpractice.com/formulas/topic/text) · [Date formulas](https://spreadsheetpractice.com/formulas/topic/date) · [Dynamic array formulas](https://spreadsheetpractice.com/formulas/topic/dynamic) · [Duplicates and data cleanup formulas](https://spreadsheetpractice.com/formulas/topic/cleanup) · [Rounding and math formulas](https://spreadsheetpractice.com/formulas/topic/math) · [Percentage formulas](https://spreadsheetpractice.com/formulas/topic/percent) · [Formula error fixes](https://spreadsheetpractice.com/formulas/topic/errors) · [Time formulas](https://spreadsheetpractice.com/formulas/topic/time) · [Google Sheets formulas](https://spreadsheetpractice.com/formulas/topic/sheets)

Every answer: [Formula answers](https://spreadsheetpractice.com/formulas). Formulas used: `PMT`, `ROUNDUP`, `NPER`, `RATE`, `FV`, `PV`, `NPV`, `SUMPRODUCT`, `IRR`, `ROWS`, `LET`, `COUNTIF`, `INDEX`, `EXP`, `LN`, `PRODUCT`, `COUNT`, `ARRAYFORMULA`, `AVERAGE`, `SUM`

---

Spreadsheet Practice (https://spreadsheetpractice.com) teaches spreadsheet formulas by practice: every exercise is graded in a live grid and re-checked on changed data.