Evergreen College holds an endowment currently worth $700 million (an endowment

Question

Evergreen College holds an endowment currently worth $700 million (an endowment is a permanent fund of money that generates income and also can be used to pay for projects). The Chief Investment Officer of the endowment has hired you to predict the performance of the endowment over the next 10 years.

Given the current investment strategy, money invested in the endowment is expected to grow by 8% per year. Over the next 7 years, Evergreen’s development office is running a major capital campaign among its alumni. During the entire 7-year campaign the college expects to collect a total of $300 million in contributions that will be added to the endowment, although the campaign may collect a total as low as $150 million and as high as $400 million.

A portion of the endowment is also spent each year to support ongoing activities at the college (this is called the draw). During this past year, the draw was $65 million. During the next ten years the draw in each year will be calculated according to a formula that is the sum of two terms: (i) 70% of the previous year’s draw and (ii) a certain percentage (p) of the value of the endowment at the beginning of the year. This percentage p depends on the level of the endowment itself, and is given in the following table:

For example, if the endowment is $650 million at the beginning of the year, then the draw is equal to (70%)*(previous year’s draw) + (2%)*($650 million).

a. Assume that the capital campaign collects $300 million.

Build a spreadsheet to calculate the value of the endowment after the next 10 years. Your spreadsheet will be graded both for its technical correctness and for its adherence to the principles of spreadsheet engineering. Note that documentation of each calculation is not required, nor is extensive formatting for appearance. However, if you need to make any assumptions beyond the problem description given above, you should include an explanation of these assumptions at the top of the spreadsheet.

            Endowment value at the end of year 10: _________________

Endowment value (millions) draw percentage (p) Below $ 500 0.0% $500-600$ 1.0% $600-700$ 2.0% $700-800$ 2.5% $800 or above 3.0%

Explanation / Answer

Here all the units are in millions.

the endowment at the end of each year becomes the endowment for the next year.

but for the 8th year it is different because the fund collected by the capital campaign in 7 years is added to the endowment at the end of 7 years. so $300 million will be added to the endowment at the end of 7 years which is $637.724932million. so the endowment at the begining of 8th year becomes $637.724932+$300=$937.724932 million.

and finally the endowment at the end of ten years is $958.3605million

year Endowment at starting of year draw for that year endowment at the end of year without considering draw endowment at the end of the year considering draw 1 $700 $65 $765 $635 2 $635 $58.2 $685.8 $627.6 3 $627.6 $53.292 $677.808 $624.516 4 $624.516 $49.7942 $674.47728 $624.68256 5 $624.68256 $47.3499552 $674.657165 $627.30721 6 $627.303721 $45.6911128 $677.491787 $631.80067 7 $631.80067 $44.6197992 $682.344724 $637.724932 8 $937.724932 $59.3656074 $1012.74293 $953.37732 9 $953.37732 $70.15272248 $1029.64751 $959.49027 10 $959.49027 $77.8889881 $1036.24949 $958.3605

Related Questions

Navigate

• Browse (All)
• Browse E
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.