CAGR formula in Excel: growth rates, compound interest and loan payments

In short: CAGR is =(end/start)^(1/years)-1. Compound interest is =principal*(1+rate)^years or =FV(rate,years,0,-principal). A monthly loan payment is =PMT(rate/12,years*12,-loan).

All figures on this page are made up for practice. They are calculation examples, not financial advice.

Try it now: CAGR from a revenue table

Revenue went up and down a little between 2020 and 2025. Find the single yearly growth rate that gets you from the first value to the last.

Task In E2, calculate the compound annual growth rate (CAGR) of revenue from 2020 to 2025: ending value divided by beginning value, raised to 1 over the number of years, minus 1.

	A	B	C	D	E
1	Year	Revenue $k
2	2020	1240		CAGR
3	2021	1310
4	2022	1475
5	2023	1520
6	2024	1690
7	2025	1835

The CAGR formula in Excel

Part	Meaning	In the example
end/start	Total growth factor over the whole period	B7/B2 = 1.48
^(1/years)	Takes the n-th root, turning the total factor into a yearly one. ^ means "to the power of", POWER does the same.	^(1/5)
-1	Turns the factor 1.0815 into the growth rate 0.0815	8.15% once formatted as a percentage

The most common slip is the number of years. 2020 to 2025 is five steps of growth even though the table holds six values. Let Excel count them with =(B7/B2)^(1/(A7-A2))-1. Excel also has a built in function for this: =RRI(5,B2,B7) takes the number of periods, the start value and the end value and returns the same rate.

Year over year growth rate

The growth from one year to the next is an ordinary percentage change: =B3/B2-1, formatted as a percentage and filled down. Those yearly rates are useful for spotting a bad year. For a single headline number, though, their simple average is misleading. If revenue rises 50% one year and falls 50% the next, the average says 0% growth, yet you are left with 75% of where you started. CAGR does not have that problem because it compounds.

Compound interest and FV

Interest works the same way in reverse: you know the rate and want the end value. Each year the balance is multiplied by (1 + rate), so after n years it has been multiplied n times.

Task A deposit earns interest every year, and the interest earns interest too. In B5, calculate the value after the number of years in B3.

	A	B
1	Deposit $	10000
2	Annual rate	0.03
3	Years	8
4
5	Future value $

=B1*(1+B2)^B3 gives 12,667.70, where simple interest (=B1*(1+B2*B3)) would give 12,400. Keep the brackets around 1+B2, because Excel applies the power before the multiplication. FV gets you the same answer: =FV(B2,B3,0,-B1). Its arguments are rate, number of periods, regular payment and present value. The payment argument makes FV useful for savings plans: =FV(B2/12,B3*12,-100,-B1) adds a deposit of 100 every month with monthly compounding.

The sign convention

FV, PV and PMT treat every amount as cash flowing in or out. Money you pay out is negative, money you receive is positive. Deposit 10,000 and the present value is -10,000, so FV returns a positive balance that you get back at the end. A loan is the other way round: the amount you borrow comes in, so it is positive, and the payments you make go out, so PMT returns them as negative numbers. Put a minus in front of either the loan or the function when you want to see a positive payment.

Monthly loan payment with PMT

A standard repayment mortgage or car loan has a fixed payment every month. PMT works it out from the rate, the number of payments and the amount borrowed.

Task In B5, use PMT to calculate the monthly payment on the loan. Show the payment as a positive number.

	A	B
1	Loan $	200000
2	Annual rate	0.04
3	Term in years	20
4
5	Monthly payment $

Argument	Meaning	In the example
rate	Interest rate per payment period, so the annual rate divided by 12 for monthly payments	B2/12
nper	Total number of payments, years times 12	B3*12
pv	Present value, the amount borrowed	-B1
fv, type	Optional: balance left at the end, and 0 for payments at the end of the period or 1 for the start	empty

For a loan with a balloon payment, say 50,000 still owed after ten years, add it as the fourth argument: =PMT(B2/12,10*12,-B1,50000). To build a full amortization schedule, list the balance month by month and lock the rate and payment cells with absolute references.

Common mistakes

Annual rate with monthly periods

Passing the yearly rate to PMT while counting months gives a payment far too high. Rate and periods always need the same unit.

Percent typed as a whole number

A cell holding 4 instead of 4% means 400% to Excel. Type 4% with the percent sign, or 0.04.

Wrong number of years in CAGR

Count the gaps between the first and last year, not the number of rows.

Long decimals in the payment

Use a currency format for display, and ROUND to two decimals if the value feeds further calculations.

These functions in German Excel

English	German
=PMT(B2/12,B3*12,-B1)	=RMZ(B2/12;B3*12;-B1)
FV, PV	ZW, BW
POWER	POTENZ
RRI	ZSATZINVEST

German Excel separates arguments with semicolons and uses a decimal comma. A formula built with ^ looks the same in both languages.

Which Excel versions have these functions?

PMT, FV, PV and POWER are available in every current version of Excel. Microsoft documents RRI for Microsoft 365, Excel 2024, 2021, 2019 and 2016, on Windows and Mac. The ^ formula works everywhere.

FAQ