Question

business has just take of prinsipal? (4 po J.b ornows $2,000,000 from his is mother ilaw likes him so much, she will charge him only 40% ¡terest tatt after makes the 22nd payment? points) dustries has a project that has the following cash flows Year Cash Flow $400.00 200.00 145.43 195.12 at cash flow will the project have to generate in the fourth year in order for the project to bave 109 0%e of return?

Using financial calculator when applicable

Answer 1

Solution a) Loan amortization Schedule

Following are the steps to be followed on Microsoft Excel to calculate the Monthly Installment:

Step 1: Click on "FORMULAS" tab at the top of Microsoft Excel
Step 2: Select the option "Financial"
Step 3: Under "Financial" select the option "PMT"
Step 4: Insert Rate = 0.11/12 Nper = 12 PV = -72500

PMT = $6407.67

Following are the steps to be followed on Microsoft Excel to calculate the Interest payment:

Step 1: Click on "FORMULAS" tab at the top of Microsoft Excel
Step 2: Select the option "Financial"
Step 3: Under "Financial" select the option "IPMT"
Step 4: Insert Rate = 0.11/12 Per = 1 Nper = 12 PV = -72500

IPMT = $664.58

Principal payment = Total payment – Interest payment

Ending balance = Beginning balance – Principal payment

Keeping the Rate, Nper and PV as constant drag the formula till 12^th Month, you will derive the schedule

Month	Beginning Balance	Total Payment	Interest Payment	Principal Payment	Ending Balance
1	72500.00	6,407.67	664.58	5,743.09	66,756.91
2	66,756.91	6,407.67	611.94	5,795.73	60,961.18
3	60,961.18	6,407.67	558.81	5,848.86	55,112.32
4	55,112.32	6,407.67	505.20	5,902.47	49,209.85
5	49,209.85	6,407.67	451.09	5,956.58	43,253.27
6	43,253.27	6,407.67	396.49	6,011.18	37,242.09
7	37,242.09	6,407.67	341.39	6,066.28	31,175.80
8	31,175.80	6,407.67	285.78	6,121.89	25,053.91
9	25,053.91	6,407.67	229.66	6,178.01	18,875.90
10	18,875.90	6,407.67	173.03	6,234.64	12,641.26
11	12,641.26	6,407.67	115.88	6,291.79	6,349.47
12	6,349.47	6,407.67	58.20	6,349.47	0.00

The second Month’s Principal repayment is $5795.73

% of Monthly payment for Principal repayment for the 2^nd Month

= Principal Repayment for second month / Total payment x 100

= 5795.73 / 6407.67 x 100

= 90.45%

Solution b:

Following are the steps to be followed on Microsoft Excel to calculate the Monthly Installment:

Step 1: Click on "FORMULAS" tab at the top of Microsoft Excel
Step 2: Select the option "Financial"
Step 3: Under "Financial" select the option "PMT"
Step 4: Insert Rate = 0.4/12    Nper = 24 PV = -2000000

PMT = $122,357.47

Following are the steps to be followed on Microsoft Excel to calculate the Interest payment:

Step 1: Click on "FORMULAS" tab at the top of Microsoft Excel
Step 2: Select the option "Financial"
Step 3: Under "Financial" select the option "IPMT"
Step 4: Insert Rate = 0.4/12 Per = 1 Nper = 24 PV = -2000000

IPMT = $66,666.67

Principal payment = Total payment – Interest payment

Ending balance = Beginning balance – Principal payment

Keeping the Rate, Nper and PV as constant drag the formula till 12^th Month, you will derive the schedule

Month	Beginning Balance	Total Payment	Interest Payment	Principal Payment	Ending Balance
1	20,00,000.00	1,22,375.47	66,666.67	55,708.80	19,44,291.20
2	19,44,291.20	1,22,375.47	64,809.71	57,565.76	18,86,725.43
3	18,86,725.43	1,22,375.47	62,890.85	59,484.62	18,27,240.81
4	18,27,240.81	1,22,375.47	60,908.03	61,467.44	17,65,773.36
5	17,65,773.36	1,22,375.47	58,859.11	63,516.36	17,02,257.00
6	17,02,257.00	1,22,375.47	56,741.90	65,633.57	16,36,623.43
7	16,36,623.43	1,22,375.47	54,554.11	67,821.36	15,68,802.08
8	15,68,802.08	1,22,375.47	52,293.40	70,082.07	14,98,720.01
9	14,98,720.01	1,22,375.47	49,957.33	72,418.14	14,26,301.87
10	14,26,301.87	1,22,375.47	47,543.40	74,832.08	13,51,469.80
11	13,51,469.80	1,22,375.47	45,048.99	77,326.48	12,74,143.32
12	12,74,143.32	1,22,375.47	42,471.44	79,904.03	11,94,239.29
13	11,94,239.29	1,22,375.47	39,807.98	82,567.49	11,11,671.80
14	11,11,671.80	1,22,375.47	37,055.73	85,319.74	10,26,352.05
15	10,26,352.05	1,22,375.47	34,211.74	88,163.74	9,38,188.32
16	9,38,188.32	1,22,375.47	31,272.94	91,102.53	8,47,085.79
17	8,47,085.79	1,22,375.47	28,236.19	94,139.28	7,52,946.51
18	7,52,946.51	1,22,375.47	25,098.22	97,277.25	6,55,669.26
19	6,55,669.26	1,22,375.47	21,855.64	1,00,519.83	5,55,149.43
20	5,55,149.43	1,22,375.47	18,504.98	1,03,870.49	4,51,278.94
21	4,51,278.94	1,22,375.47	15,042.63	1,07,332.84	3,43,946.10
22	3,43,946.10	1,22,375.47	11,464.87	1,10,910.60	2,33,035.50
23	2,33,035.50	1,22,375.47	7,767.85	1,14,607.62	1,18,427.88
24	1,18,427.88	1,22,375.47	3,947.60	1,18,427.88	0.00

Therefore, the outstanding balance after Mr. A makes 22^nd payment is $233,035.50

Solution c: Let the Cash inflows for the last year be x.

To generate the return of 10%, the Total Present Value of the Cash inflows of the project should be $400 at 10% Discounting Factor.

Year	Cash Inflows	Discounting Factor @ 10%	Present Value of Cash Inflows
1	200.00	0.9091	181.81
2	145.43	0.8264	120.19
3	195.12	0.7513	146.59
4	x	0.6830	0.6830x
			448.6 + 0.6830x

Therefore, 448.6 + 0.6830x = 400
                    0.6830X = 400 - 448.6
                         x = -71.16

Hence, for the project to generate a return of 10%, the Cash inflows for the 4^th year should be -$71.16