QUESTION 5 From: Principles of Finance with Excel 3rd ed., Benninga and Mofkadi,

Question

QUESTION 5 From: Principles of Finance with Excel 3rd ed., Benninga and Mofkadi, ® 2018,2011, 2006 Mary has just completed her undergraduate degree from Northwestern University ad is already planning to enter an MBA program 4 years from today. The MBA tuition will be $50,000 per year for 2 years, paid at the beginning of each year. In addition, Mary would like to retire 15 years from today and receive a pension of $60,000 every year for 20 years with the first pension payment paid out 15 years from today. Mary can borrow and lend as much as she likes at a rate of 7% compounded annually. In order to fund her expenditures, Mary will save money at the end of years 0-3 and at the end of vears 6 through 14 Calculate the constant annual dollar amount that Mary must save at the end of each of these years to cover all her expenditures (tuition and retirement). Hint: Build a C.F. /Amort. Table and It will be helpful to use either GoalSeek or Solver Borrow/Lend Rate Annual Savings 7.00% Tuition/Yr Pension/Yr 50,000.00 60,000.00

Explanation / Answer

Mary has a series of cash inflows (in the form of savings) and cash outflows (in the form of MBA tuition fess and pensions) over the next few decades assuming that current time is t= 0 or end of Year 0.

Mary's Borrowing and Lending Rate = Prevailing Interest Rate (for compounding/discounting) = 7 %

Cash Outflows:

Tuition Fees = $ 50000 at the beginning of year 5 (as fee is paid in advance and the program begins 4 years from today or at the end of Year 4/beginning of Year 5). Similarly the second fee is paid at the beginning of Year 6 (end of Year 5).

PV (present value) of tuition fees today = 50000 / (1.07)^(4) + 50000 / (1.07)^(5) = $ 73794.07

Retirement Pensions = $ 60000 beginning 15 years from now or at the end of Year 15 and continues for the next 20 years, thereby ending at the end of Year 34 (it is not 35 because the first pension is received at t= 15 and hence included in the 20 pension payments).

PV of Pensions today = 60000 x (1/0.07) x [1-{1/(1.07)^(20)]] x 1/(1.07)^(14) = $ 246512.5

Total PV of Cash Outflow today = PV(Tuition Fees) + PV(Pensions) = 73794.07 + 246512.5 = $ 320306.6

Cash Inflow:

Cash Inflow would be annual savings at the end of Year 0. Year 1, Year 2 and Year 3 followed by further savings at the end of Year 6 through Year 14.

Let the annual savings be $ K

As cash inflow = cash outflow for the savings to suffice for the MBA Tuition Fees and post retirement pensions.

Therefore, K + K x (1/0.07) x [1-{1/(1.07)^(3)}] + K x (1/0.07) x [1-{1/(1.07)^(9)}] x 1 / (1.07)^(5) = $ 320306.6

Using EXCEL's Goal Seek Function to solve the above equation we get:

K = $ 38733.085 ~ $ 38733

Related Questions

Navigate

• Browse (All)
• Browse Q
• Subjects

---

---

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