Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Retirement savings in excel
hi,
how can we calculate in excel that how long the retirement savngs lets say $100,000 will last for? |
#2
|
|||
|
|||
is it nper?
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Retirement savings in excel
"Umesh Banga" wrote:
how can we calculate in excel that how long the retirement savngs lets say $100,000 will last for? First, you need to make assumptions about the growth rate (interst) and how much you will withdraw each year. Suppose the average growth rate is 4%. Your savings will last forever, even grow, if your withdrawal is no more than $4000 per year (4% of $100,000). But suppose you must withdraw $8000 per year. Then the NPER function tells you how long your savings will last, to wit: =NPER(4%,-8000,100000,0,1) Note: That assumes the first withdrawal is at the beginning of the first year. However, it is unrealistic to expect a constant withdrawal. Assuming the withdrawal represents expenses, expenses increase at an annual inflation rate. One way to tackle that is with an amortization schedule. For example, suppose A1 contains the amount of retirement savings (100000), A2 contains the average growth rate (4%), A3 contains the initial withdrawal (8000), and A4 contains the average inflation rate (3%). Then set up the following table: D1: =A1 B2: 1 C2: =A3 D2: =(D1-C2)*(1+$A$2) B3: =B2+1 C3: =C2*(1+$A$4) Copy D2 and paste in D3. You will note that the references to D1 and C2 change accordingly. Then select B3:D3 and drag the lower-right corner down 12 or more rows. In this example, you should see that column D becomes negative when the value in column B becomes 14. Of course, you can make the table more robust. And there is probably a Microsoft template that you could use. Use Excel Help or Google to search for a template. |
#4
|
|||
|
|||
awesome thanks.
Quote:
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I need a excel template to track my savings from various places. | New Users to Excel | |||
Setting up an Excel sheet to automatically determine when an employeeshould enter a retirement plan, perhaps What-If Analysis would help? Anyother suggestions? | Links and Linking in Excel | |||
Excel - tuition savings question | Excel Worksheet Functions | |||
Retirement Savings Last | Excel Programming |