Question

Please use Microsoft excel to solve the following questions.

1a) You borrowed a 15-year loan of $1,000,000 from a bank. Interest is compounded monthly and the annual interest rate is 2.5%. Find the monthly repayment amount.

1b) You borrowed a 10-year loan of $1,200,000 from a bank. Interest is compounded monthly and the annual interest rate is 2%. What is the total interest expense of the loan?

1c) You borrowed $50,000 from a bank 2 years ago at an unknown interest rate. You only know that interest is compounded monthly; and your monthly repayment is $3,000 with a payment holiday in August of each year. Assuming there is no handling fee or any other charges, find the APR of the loan.

1d) You borrowed $50,000 from a bank 2 years ago at an unknown interest rate. You only know that interest is compounded monthly; and your monthly repayment is $3,000 with a payment holiday in August of each year. Assuming there is no handling fee or any other charges, what is the total interest expense of the loan?

1e) You borrowed $388,800 from a bank. Interest is compounded annually and the annual interest rate is 3%. Suppose you make a yearly repayment of $30,000 every year. How many years does it take to pay off the debt? (Round up to the nearest year)

Answer 1

1a) Use the PMT function in excel

Number of periods(12 months in a year)= 15*12=180

Rate of interest per period = 0.025/12=0.00208333

Loan amount	1000000
Rate of interest per year	0.025
Rate of interest per period	0.002083333
Number of years	15
Number of periods	180
PMT	($6,667.89)

Hence, the monthly payment amount is $6667.89

1b) Using the CUMIPMT function in excel

Start_period =1

End_period = 120

Start period and end period to calculate the total interest payments between 1st installment and 20th installment.

Loan amount	1200000
Rate of interest per year	0.02
Rate of interest per period	0.001666667
Number of years	10
Number of periods	120
CUMIPMT	($122,789.09)

1c)

This question could be solved using excel solver.

Steps:

Create a payment schedule for the past 2 years( skip August payments)

We are calculating future value since we want to know the present value of payments made over the past 2 years

For eg. the future value factor for month 2 is (1+r/12)^(24-2). Here, you must give any cell reference to 'r' (Interest rate cell)

Multiply all the payments with the future value factor ( This is 'Value today' column)

Sum all the 'Value today' column entries ( This is value of the loan today)

Now, go in Data=> Solver=> Set target cell (Value of the loan today) cell => Equal to value of 0 => By changing cell ( Interest rate) => Solve

Hence, we get APR = 28.5896%

Year	Month	Month name		Future value factor	Value today ( after 2 years)
0	0	1-Jan	-50000	1.75960225	-87980.11252		Interest rate	0.285896449158952
1	1	January	3000	1.718655785	5155.967354
1	2	February	3000	1.678662156	5035.986467
1	3	March	3000	1.63959919	4918.79757
1	4	April	3000	1.601445231	4804.335694
1	5	May	3000	1.564179127	4692.53738
1	6	June	3000	1.527780215	4583.340645
1	7	July	3000	1.492228317	4476.684951
1	8	August	0	1.457503722	0
1	9	September	3000	1.423587179	4270.761536
1	10	October	3000	1.390459883	4171.37965
1	11	November	3000	1.35810347	4074.31041
1	12	December	3000	1.3265	3979.5
2	13	January	3000	1.295631952	3886.895857
2	14	February	3000	1.265482213	3796.446639
2	15	March	3000	1.236034067	3708.102201
2	16	April	3000	1.207271188	3621.813565
2	17	May	3000	1.17917763	3537.53289
2	18	June	3000	1.151737817	3455.213452
2	19	July	3000	1.124936537	3374.809612
2	20	August	0	1.098758931	0
2	21	September	3000	1.073190485	3219.571455
2	22	October	3000	1.048217025	3144.651074
2	23	November	3000	1.023824704	3071.474112
2	24	December	3000	1	3000
				Value of the loan today	4.48858E-08

1d) Referring to the above table, enter the start period and end period as in the column. Total cumipmt is the sum of the 3 intervals of cumulative interest.

	CUMI PMT
From month 1-7	-6183.56354
From month 9-19	-6701.43722
From month 21-24	-612.768312
Total cumipmt	-13497.7691

1e.) Using the nper function in excel

PV of Loan	-388800
Rate	0.03
PMT	30000
nper	16.65599

PMT is the monthly payment.

PV of loan is taken negative since the sign of inflows and outflows should be different.

Hence, it takes approximately 17 years to pay off the debt