Question

Part A Little Jack Horner is tired of sitting in the corner and wants to attend college. In fact, he intends to go to Harvard when he is old enough and he wants to major in nursery rhyme writing. He will stay in school for 4 years after which he will have a BA degree. His father, Humpty Dumpty, is sympathetic with Jack’s aspirations, but he believes Jack must take care of him first. Therefore, Humpty will finance Jack’s education with a bequest at his death and, until that time, Jack is to look after his father. Harvard will cost $50,000 per year for the four years (payable at the beginning of each year) and Humpty specifies in his Will, that, at his death, the necessary funds will be deposited in an account paying 10% annual interest. Jack will begin college immediately after Humpty’s death. Humpty will retire 20 years before his death and during his retirement he wants to have $100,000 per year (beginning-of-year). The funds necessary for Jack’s education as well as Humpty’s retirement pay will come from an amount he will have accumulated during his remaining working years. Upon his retirement, Humpty plans to invest all of his funds into an account which will earn 10% annual interest (this rate will be earned during Humpty’s retirement years and during Jack’s college years). Currently Humpty does not have any money saved; however, he plans to retire 15 yearsfrom now. Humpty wants to accumulate enough funds over his remaining 15 working years to enable him to fulfill his plans as described above. Humpty plans to accumulate the necessary funds in two ways: 1. He just purchased 1,000 shares of SkyRocket company common stock for $20 per share. He believes that the price will increase at a 20% annual rate and he will sell the stock when he retires. He does not expect the stock to pay a dividend over the next 15 years. 2. He will put aside a fixed amount at the end of each year beginning this year (during his working years) in an IRA which he will withdraw at the end of his working period (ignore taxes). The IRA will pay 10% annual interest. What annual payment must Humpty make into the IRA account in order to carry out his plans? Please provide a timeline, a description of all of your math, and calculator inputs. Part B A firm has estimated its cost of capital as 5% and is considering a project with an initial investment of -$265,000. The subsequent cash flows are $65,000; $77,000; $83,000; $91,000; and $96,000. In the final year (year #6), the firm must pay $50,000 to clean up the site. Calculate the project’s MIRR using the three methods discussed in class. Please provide timelines, a description of all of your math, and calculator inputs.

Desired methods for part B are: Discounting Approach, Reinvestment Approach, and Combination Approach.

Answer 1

Sol (A)
First we will work the Present Value of the Retirement and Jack Education Goals set by Humpty at Retirement Time or T=0
The timeline of cash flows are as under they are then Discounted @ 10% p.a. Rate
Calculating Discount Factor = [1/(1+rate)^n] where rate =10% and n=time i.e. 0 for Year 0 and 1 for Year 1 so on till Year 24
Present Value of Cash flow = Cash Flow * Discount Factor
Our Calculation of Present Value will conclude the Amount required by Humpty at the time of Retirement is $9,74,915.39
Then we will have to make his investments for 15 years to be equal to $9,74,915.39; we would use Future Value formula for $20,000 investment in stocks, growing at rate 20% p.a.
The value of Stock after 15 years will be calculate by using FV formula: =FV(rate,nper,,-PV,0) here Rate= 0.2; nper=15; PV=-20000 after substituting these values in the formula we would get =$3,08,140.43
We are left with $9,74,915.39-$3,08,140.43= $6,66,774.96 which will be required by the IRA account where funds will grow by 10% p.a.
We will use the PMT function in the excel to find out the annual payments needed to be deposited by Humpty in each of the 15 working years.
The formula will be =PMT(rate,nper,,-FV,0). Here Rate=0.1; nper=15; FV= -666774.96 by substituting these values in the formula we will get $20,985.93
Thus Annual Payments Humpty should make in IRA account =$20,985.93
Year	Cash Flows	DF @ 10%	Present Value of Cash Flows
0	100000	1.000	100000.00
1	100000	0.909	90909.09
2	100000	0.826	82644.63
3	100000	0.751	75131.48
4	100000	0.683	68301.35
5	100000	0.621	62092.13
6	100000	0.564	56447.39
7	100000	0.513	51315.81
8	100000	0.467	46650.74
9	100000	0.424	42409.76
10	100000	0.386	38554.33
11	100000	0.350	35049.39
12	100000	0.319	31863.08
13	100000	0.290	28966.44
14	100000	0.263	26333.13
15	100000	0.239	23939.20
16	100000	0.218	21762.91
17	100000	0.198	19784.47
18	100000	0.180	17985.88
19	100000	0.164	16350.80
20	100000	0.149	14864.36
21	50000	0.135	6756.53
22	50000	0.123	6142.30
23	50000	0.112	5583.91
24	50000	0.102	5076.28
Amount Required by Humpty at the time of Retirement			974915.39

Sol (B): Using the Discounting Approach to find the MIRR, the cash flows i.e. Cash Outflows will be discounted by the cost of capital @ 5% to Time 0 i.e. Now. In our case there are only two cash outflows firstly initial outflow= -$2,65,000 at T0 so Discount Factor at Year 0 = 1 so present value =-265000*1=-265000; At year T6 an outflow of $50,000 this will be discounted @ 5% to T0 by = 50000/(1+0.05)^6 = -$37310.77 . Thus Cash outflows at T0= $265000+$37310.77 = $ 3,02,310.77 other Cash Inflows will be as it is i.e. $65,000; $77,000; $83,000; $91,000; and $96,000. We will now use the IRR function of excel i.e. =IRR(selecting the entire Cash flows i.e. -302310.77, 65,000, 77,000, 83,000, 91,000 and 96,000) using the IRR function and selecting these cash flows we would get MIRR= 10.55% using discounting approach.

Using the Re-investment Approach to find the MIRR, the cash flows i.e. Cash Inflows will be compounded by the cost of capital @ 5% to Year 1 Cash Inflow should be 65000*(1.05)^5 = 82,958.30; Year 2 Cash Inflow= 77000*(1.05)^4= 93,593.98; Year 3 Cash Inflow= 83000*(1.05)^3= 96082.88; Year 4 Cash Inflow= 91000*(1.05)^2= 1,00,327.50 and Year 5 Cash Inflow = 96000*(1.05)= 1,00,800.00 Cash Outflows will remain the same i.e. at Year 0 =-265000 and at Year 6 = -50000. The basic understanding is that inflows are being re invested till the end of the project i.e. Year 6. Using the IRR function of excel we will find the MIRR i.e =IRR(Cash flows from Year 0 to Year 6) note these cash flows which are selected are Re Investment approach strategy. The value of MIRR = 19.44% using the Re-investment approach.

Combined Approach is nothing but the Cash Outflows and Inflows we have derived using the above two approaches thus the Cash Flows are Year 0= -$3,02,310.77; Year 1= $82,958.30; Year 2= $93,593.98; Year 3= $96,082.88; Year 4= $1,00,327.50 and Year 5= $1,00,800.00. Now again using the IRR function of the Excel we would be computing =IRR (selecting the above Cash flows i.e. Cash Outflows of Year 0 till Cash Inflows of Year 5) then pressing enter we would get = 16.51%. The value of MIRR = 16.51% using the Combined Approach.