A wealthy donor endows a chaired professorship. If the interest rate is 8% how much must he set aside to provide the following end of year salary payment alternatives?
a. $100,000 per yr for 20 yrs
b. $100,000 per yr in perpetuity
c. A perpetuity that pays $100,000 for the first year and grows at 4% thereafter.
Excel to be used to solve this
You wrote, "If the interest rate is 8%" but you didn't tell us how it's compounded. So I'll assume simple interest compounded annually in the suggestions.
For a. we would need 100,000*20=2,000,000 so
2,000,000=P(1+.08)20
Solve for P
For b. you want
100,000=P*.08 since we assume the interest is fixed at 8% for perpetuity too.
For c. we want
100,000(1+.04)n=P(1+.08)n so
P=100,000* (1.04/1.08)n
This looks like a geometric series that I'll let you solve. I'll be glad to check your work. (It looks to be around 2.7mil or something close)
After rechecking the questions I'm not sure if I gave the right approach for a.
I'm not sure if I followed the question now either. Is the endowment supposed to be used entirely over that time period? I'll look at this again to see if I can make sense of it.
To solve part a. I assumed the amount was supposed to be used up completely at the end of 20yrs.
You would start with amount P, then mutiply it by 1.08 and subtract 100,000
The formula looks like (P*1.08) -100,000
I dragged that down twenty rows. Then I used the goal seek to get the twentieth row to equal 0 and pointed to another cell for the P value.
The anwer I got was P = 981,814.74
Well, if you're using Excel to solve it, just make sure you don't accidentally delete all your calculations in a fit of rage. We don't want you to Excel at that.
To solve part a using Excel, follow these steps:
1. Open a new Excel spreadsheet.
2. In cell A1, enter the initial amount P.
3. In cell A2, enter the formula =A1*1.08-100000. This calculates the value after one year and subtracts the salary payment.
4. Drag this formula down to cell A21. This will calculate the values for each subsequent year.
5. In cell A22, enter the formula =A21. This copies the value from the 20th year to the 21st year.
6. In cell B22, enter the formula =A22-100000. This calculates the difference between the 21st year value and the final remaining salary payment.
7. Go to the "Data" tab and click on "What-If Analysis". Select "Goal Seek" from the dropdown menu.
8. In the Goal Seek dialog box, set the "Set Cell" to be A22 and the "To Value" to be 0.
9. In the "By Changing Cell" field, enter A1. This tells Excel to find the value of P that makes the 21st year equal to 0.
10. Click on "OK" and Excel will calculate the value of P that satisfies the condition.
11. The value of P will be displayed in cell A1, which is approximately $981,814.74.
Therefore, the donor must set aside approximately $981,814.74 to provide a salary payment of $100,000 per year for 20 years at an interest rate of 8%.