I am working on building a financial planning tool and need to verify the results of the Present Value of A Growing Annuity formula. I'm using the following formula:
$PV = Pmt x (1 - (1 + g)n x (1 + i)-n ) / (i - g)$
Where:
- PMT = Periodic Payment
- g = periodic growth rate of the payment
- i = periodic growth rate of the principal
- n = number of periods
While this formula itself works as expected, when I construct a table to show the change for each period, the numbers never balance. I've tried several approaches:
- (beginning of period principal x (1+i)) - (periodic payment x (1+g))
- (beginning of period principal - (periodic payment x (1+g))) x (1+i)
- (beginning of period principal - periodic payment) x (1+(i-g))
- and other less memorable approaches
Here's a link to a table I've built with some of these approaches. I've used sample data of:
- PMT = 48,000
- g = 3.22%
- i = 7.8%
- n = 50
This produces a value of $928,462.67.
When I try to reproduce the periodic steps of this formula in Excel, the principal is exhausted in period 33. What I want is a table that can show annual or monthly payments that align to this result, OR I would appreciate guidance on how to break down the formula to recreate the steps myself so that I can show the periodic payment and principal by period.
The problem with my approach above was an issue of sequencing the steps. Since the payment is received at the end of the first year, the principal should grow for the first period while the payment should not. Once this is correctly structured, the PVGA formula matches the deconstructed results. You can see this laid out in the file I shared above.