CONTEXT

A bank offers a bridging loan of 100,000 at a fixed annual rate of 3% over 240 months. This rate is revisable after 12 months.

INITIAL FORECAST

1) a) Calculate the value of the equivalent monthly rate (to the nearest 0.01%):

t = ..

1) b) Calculate the amount of the monthly amortization payment (to the nearest euro cent):

M = ...

1) c) Deduce the estimated cost of the loan (to the nearest euro):

S = .

On the spreadsheet, using the calculation formulas given below, fill in the columns up to the last monthly payment.

REVISION OF THE RATE

2) a) After one year, the bank readjusts the rate. It now stands at T.

On the spreadsheet to the right of the Annual rate box, enter the value T given to you by the teacher.

Calculate the monthly rate to apply from the 13th month (to the nearest 0.01%):

t = .

2) b) It is assumed that the borrower wishes to keep the same monthly payment as before, even if the total duration of the loan is modified.

Resume filling in the columns from maturity 13 with this new rate until the loan is fully repaid (that is to say, until a capital residue is less than the monthly payment).

2) c) What then is the revised term of this loan?

N = ..

2) d) What is the revised cost of the loan (to the nearest euro)?

S = ..

3) a) It is assumed here that the borrower chooses instead to revise the monthly payment to keep a total duration of 240 months (therefore 228 months after revision), calculate the new monthly payment from the outstanding capital after 12 installments:

M = ..

3) b) Then calculate the total cost of the loan:

S = .

3) c) What is the best choice for the borrower?

Calculation formulas

Monthly rate for T nominal (annual) rate by the actuarial method:.

Monthly payment: where N is the amortization period in months and C is the capital due at the start of the period.

Total cost of the loan after N monthly payments with CN any residual to be repaid (final monthly payment incomplete).

Initial capital at the Nth maturity:

Interest at the Nth due date

Amortization at the Nth due date:

Final capital at the Nth maturity: