Maribeth Shoppers manages scholarship endowments for a major public university. Presently, she is trying to determine...

Transcribed Image Text:

Maribeth Shoppers 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 $542,000. The endowment's funds are invested in a portfolio whose annual return varies and may be represented as random percentage between 3% and 6% (so it is equally likely to be 3, 4, 5, or 6%, meaning you can use the =RANDBETWEEN(3,6) function in Excel, but make sure you convert your integer into a percentage as you apply your percentage in your simulation calculations). The legal terms of the endowment require Maribeth 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 dropping below its current value. Assume scholarship payments are withdrawn from the fund at the end of each year, so that for the first year, the full initial balance of $542,000 is bearing interest. Use simulation to answer this question. Try out different payout amounts to estimate how much would be an ideal scholarship payment that should be made in the next 10 years. Maribeth Shoppers 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 $542,000. The endowment's funds are invested in a portfolio whose annual return varies and may be represented as random percentage between 3% and 6% (so it is equally likely to be 3, 4, 5, or 6%, meaning you can use the =RANDBETWEEN(3,6) function in Excel, but make sure you convert your integer into a percentage as you apply your percentage in your simulation calculations). The legal terms of the endowment require Maribeth 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 dropping below its current value. Assume scholarship payments are withdrawn from the fund at the end of each year, so that for the first year, the full initial balance of $542,000 is bearing interest. Use simulation to answer this question. Try out different payout amounts to estimate how much would be an ideal scholarship payment that should be made in the next 10 years.