Question

Meredith Shomers manages scholarship endowments for a major public university. Presently, she is trying to determine how much scholarship money may be awarded from an endowment with a current balance of $538,000. The endowment’s funds are invested in a portfolio whose annual return varies and may be represented as a normally distributed random variable with a mean of 6% and standard deviation of 2%. The legal terms of the endowment require Meredith to determine a constant scholarship payment amount from the endowment that, if made in each of the next 10 years, would result in only 5% chance of the endowment’s ending value drop- ping below its current value. Assume scholarship payments are withdrawn from the fund at the end of each year.
a. Create a spreadsheet model for this problem.

b. What is the maximum scholarship payment that should be made in the current
year?

Answer 1

a) The spreadsheet model is following

Formulas:

B9 =E8

C8 =NORMINV(RAND(),6%,2%)

D8 =$C$3

E8 =B8*(1+C8)-D8

Copy these formulas upto year 10 (row 17)

b) 5% chance of ending value dropping below current value means 5% chance of annual return being lower than the scholarship amount.

z-stat for 5% = NORMSINV(5%) = -1.645

Therefore, maximum scholarship payment as percentage of current value of endowment fund = 6% - 1.645*2% = 2.71 %

Maximum scholarship payment that should be made in the current year = 538000*2.71% = $ 14,581