Growing Annuities
Feb
13
Written by:
Charles Flock
2/13/2011 3:43 PM
We added 4 new functions to deal with growing annuities, where the initial payment increases by a fixed percentage each period, making it far simpler to answer questions like how much do I need to save for retirement?
The math for the calculation of things like the present value of an annuity, the future value of annuity, the number of periods, and the rate, are pretty well established. But they are all static, in that they assume that they payment doesn’t change over the life of the annuity. There is a very simple adjustment to the standard annuity calculations that allow you to increase the payments steadily, over time. This requires two rates be passed into the calculation; a growth rate and the rate of return that you expect the annuity to earn.
We have created 4 new functions that incorporate this feature:
· PVGA – Present Value of a Growing Annuity
· FVGA – Future Value of a Growing Annuity
· PMTGA – Payment amount for a Growing Annuity
· NPERGA – Number of Periods for a Growing Annuity
Let’s start with the Future Value function. Say you make $50,000 per year and you plan on saving 4% of your salary in a tax-deferred account. You expect your salary to grow 3% per year and you expect your account to earn 6%. What it value of the account after 40 years?
You can enter the following SQL to answer that question.
This produces the following result.
Is that going to be enough to retire on? Well, in 40 years’ time, your salary will be approximately $161,000. So, your retirement account will have a little more than 3 years’ salary in it. What if we looked at that in present day dollars?
We can do that quite simple by using the present value of a growing annuity function, PVGA. It has exactly the same inputs as the FVGA calculation.
This produces the following result.
The present value is actually less than one year’s salary.
So, how much can you withdraw each year, assuming that your tax-deferred account is going to continue to earn 6% and that you expect to live another 25 years?
That’s actually a straight annuity calculation.
This produces the following result.
You will have approximately $36,630 to live on, per year. This is less than 25% of what your projected salary is at the time retirement.
Let’s assume that you can live on half of your retirement salary, or $80,500 per year at the time of retirement, and you plan on making withdrawals for 25 years. Again assuming that your tax-deferred account continues to earn 6% per year, how much do you need to have saved?
Wait, we have not factored inflation into the equation. Let’s assume that we want maintain our purchasing power at the time of retirement and that we expect inflation to be 2%. How much would we have in our tax-deferred account at the time of retirement?
This produces the following result.
We can figure out what our payments need to be, starting today, to reach our retirement figure.
This produces the following result.
A little more than 10% of salary would have to be saved.
We can use the SeriesFloat function to create a table that shows how the investment return affects payment that we have to make.
We enter this SQL, which will calculate the payment amount based on investment returns from 1% to 12% in increments of 1%.
This produces the following result.
We can see that if we want to start with a $2,000 investment and increase that investment 3% every year, the rate of return in the tax-deferred account will have to be almost 9% over 40 years. That will allow us to withdraw 50% of our retirement year salary for 25 years.
Finally, let’s answer the following question. If I am going to save $3000 per year in my tax-deferred account, and I expect my contribution to increase 3% per year and the account’s rate of return is 6%, how many years until I have $1 million in the account?
This produces the following result.
45 years to save a million dollars. That seems like a long time. How many years to save a half-million dollars?
This produces the following result.
It takes 35 years to save a half-million dollars and 45 years to save a million dollars, which means that during the last 10 years the tax-deferred account grew as much as it did in the first 35 years.
We think that these functions are a nice addition to the time-value-of-money capabilities in XLeratorDB. They certainly simplify what might be an otherwise complicated calculation. Let us know what you think.