CAGR explanation and calculation in MS Excel using the Goalseek tool (1 of 4 ways- all links below)
676 بار بازدید -
4 سال پیش
-
Follow Along File-
Follow Along File- https://auditexcel.co.za/product-down...
00:00 Explanation of Compound Annual Growth Rate (CAGR)
00:33 Set up the 'buckets'
01:09 Goalseek the CAGR
01:52 Further CAGR explanation
Improve Goalseek precision- Goalseek precision (where goalseek ge...
CAGR with the RATE function- CAGR (Compound Annual Growth Rate) us...
CAGR with the IRR function- CAGR using MS Excel's IRR Function. T...
CAGR with the XIRR function- CAGR by using the XIRR Function in MS...
One method is to create a bucket that uses a constant percentage to grow one number to another number over a number of periods and then goal seek what percentage will give you the desired result.
To follow along go to 17.14
In the example below we want to know what the CAGR is of Sales going from 10 million to 17.5 million over 3 years.
Note that this is a steady increase of 2.5 million per year but the growth each year is different as the starting base will differ i.e. 2.5 million on 10 million is 25% but 2.5 million on 12.5 million is 20%.
In the bucket we have created below we start with a closing balance of 10 million (this is the end of year sales number) and then have a growth row that takes the opening balance and determines how much it grows based on the percentage in B15. We know that we want cell G16 to be equal to 17.5 million so what percentage in B15 will create this.
Using Goal Seek
we can ask Excel to set cell G16 to be 17.5 million by changing cell B15.
As shown below, the number that works is 20.51%. Note that in year 2 and 3 the resultant sales don’t match. It is only in the final year that it matches. This is because we are trying to find the consistent percentage growth that will arrive at the number we want.
00:00 Explanation of Compound Annual Growth Rate (CAGR)
00:33 Set up the 'buckets'
01:09 Goalseek the CAGR
01:52 Further CAGR explanation
Improve Goalseek precision- Goalseek precision (where goalseek ge...
CAGR with the RATE function- CAGR (Compound Annual Growth Rate) us...
CAGR with the IRR function- CAGR using MS Excel's IRR Function. T...
CAGR with the XIRR function- CAGR by using the XIRR Function in MS...
One method is to create a bucket that uses a constant percentage to grow one number to another number over a number of periods and then goal seek what percentage will give you the desired result.
To follow along go to 17.14
In the example below we want to know what the CAGR is of Sales going from 10 million to 17.5 million over 3 years.
Note that this is a steady increase of 2.5 million per year but the growth each year is different as the starting base will differ i.e. 2.5 million on 10 million is 25% but 2.5 million on 12.5 million is 20%.
In the bucket we have created below we start with a closing balance of 10 million (this is the end of year sales number) and then have a growth row that takes the opening balance and determines how much it grows based on the percentage in B15. We know that we want cell G16 to be equal to 17.5 million so what percentage in B15 will create this.
Using Goal Seek
we can ask Excel to set cell G16 to be 17.5 million by changing cell B15.
As shown below, the number that works is 20.51%. Note that in year 2 and 3 the resultant sales don’t match. It is only in the final year that it matches. This is because we are trying to find the consistent percentage growth that will arrive at the number we want.
4 سال پیش
در تاریخ 1399/12/24 منتشر شده
است.
676
بـار بازدید شده