Help with Formula!

[Hirtle22]

I am making a retirement calculator. I am charting their assets vs expenses and showing how long their money will last. Say the person is 55 and they want to retire at age 65; this means that person has 10 more years of contributions (annual in this case) to put towards their retirement. I need to figure out how to show these additions for 10 years and then cut it off after that and start showing depreciation of assets. I need the chart data to automatically changed based on the input "Target retirement age" cell. Hopefully this makes sense. Thanks for any help!

 

[skywriter]

I could help you with a formula but I don't understand the logic needed for such a formula.
There are some financial functions built in to Excel also, but I don't know which if any of them would be appropriate for your request.

You might try searching google or whatever your choice is for Excel Retirement Planner Templates or Excel Financial Planner Template. I got quite a few hits for these from Microsoft.com and other sources.

 

[Hirtle22]

Current					At Retirement
Your Current Age		55		Annual Pension Benefit ($)		0
Annual Income ($)		$ 50,000.00		Annual Pension Benefit Increases (%)		0.00%
Annual Inflation and income Increases	3.00%		Desired Retirement Age		76
Retirement Savings Balance		$ 60,000.00		Number of Years of Retirement Income		20
Annual Savings Contributions		$ 15,000.00		Income Replacement (%)		75.00%
Annual Savings Increases		0.00%		Investment Return (%)		6.25%
Investment Return		4.00%
						Yearly		Desired	Pension	Year Ending
Age	Salary	Balance		Interest	Savings		Retirement Income	Income	Balance
55	$50,000	$60,000	$2,400	$15,000	$0	$0	$77,400
56	$51,500	$77,400	$3,096	$15,000	$0	$0	$95,496
57	$53,045	$95,496	$3,820	$15,000	$0	$0	$114,316
58	$54,636	$114,316	$4,573	$15,000	$0	$0	$133,888
59	$56,275	$133,888	$5,356	$15,000	$0	$0	$154,244
60	$57,964	$154,244	$6,170	$15,000	$0	$0	$175,414
61	$59,703	$175,414	$7,017	$15,000	$0	$0	$197,430
62	$61,494	$197,430	$7,897	$15,000	$0	$0	$220,328
63	$63,339	$220,328	$8,813	$15,000	$0	$0	$244,141
64	$65,239	$244,141	$9,766	$15,000	$0	$0	$268,906
65	$67,196	$268,906	$10,756	$15,000	$0	$0	$294,663
66	$69,212	$294,663	$11,787	$15,000	$0	$0	$321,449
67	$71,288	$321,449	$12,858	$15,000	$0	$0	$349,307
68	$73,427	$349,307	$13,972	$15,000	$0	$0	$378,279
69	$75,629	$378,279	$15,131	$15,000	$0	$0	$408,410
70	$77,898	$408,410	$16,336	$15,000	$0	$0	$439,747
71	$80,235	$439,747	$17,590	$15,000	$0	$0	$472,337
72	$82,642	$472,337	$18,893	$15,000	$0	$0	$506,230
73	$85,122	$506,230	$20,249	$15,000	$0	$0	$541,479
74	$0	$541,479	$33,842	$0	$48,221	$0	$527,100
75	$0	$527,100	$32,944	$0	$49,668	$0	$510,376
76	$0	$510,376	$31,899	$0	$51,158	$0	$491,117
77	$0	$491,117	$30,695	$0	$52,693	$0	$469,119
78	$0	$469,119	$29,320	$0	$54,274	$0	$444,165
79	$0	$444,165	$27,760	$0	$55,902	$0	$416,023

<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

***This is a template from Microsoft. This is pretty much exactly what I want to do, only I want to build my own version. The formula that I cannot figure out is the one that stops adding value based on the input. For example, when the person chooses to retire, in this case 76, his or her salary ceases at that same age. What is the formula to make this same thing happen in my spreadsheet?!