Question

Total 500.000 Can you show the solution on the excel?

Semih also spoke with a friend of his who works at Güneş Bank and he received the following offer monthly payments of 10,000 TL at a 1.05% monthly interest rate, followed by a lump sum payment of 60,000 TL at the end of the term. Payments are made at the end of the period. What is the total term of the loan? Give your answer in terms of months a) b) If Semih wants to pay off the loan within 5 years (i.e. 60 months), how much should he increase the lump sum payment to be paid at the end of the term? Instead of increasing the lump sum payment, perhaps Semih will just borrow less. How many can he borrow if he wants to pay off the loan in 5 years?

Answer 1

Requirement-(b Monthly Interest rate, RATE Number of Monthly payments, NPER Loan amount, PV Bal. in loan acount at the end, FV Monthly installment type, TYPE Monthly payments required 1.05% 60 [5 years x 12; as there are 12 months in a year] 500,000 TL 60,000 TL-Lumpsum payable at the last month] 0-0 if payments made at the end of each month; 1 if payments made at the beginning of each month 10,551.49 TL [Ref: NOTE] NOTE Monthly payments required is calculated using EXCEL FUNCTION PMT(rate, n perpv,f,type) where rate-1.05%; nper-60; pV-500000; fv-60000, type:0; Here value for pv is negative because it denotes cash inflow; fv is positive as it denotes cash outflow; type 0 as monthly payments occur at the end of each month. 1 Monthly Interest rate, RATE 2 Number of Monthly payments, NPER 3 Loan amount, PV 4 Bal. in loan acount at the end, FV 5 Monthly installment type, TYPE 6 Monthly payments required 1.05% 60 [5 years x 12; as there are 12 months in a year] 500,000 TL 60,000 TL -Lumpsum payable at the last month] 0 0 if payments made at the end of each month; 1 if payments made at the beginning of each month -PMT(B1,B2,-B3,B4,B5) [Ref: NOTE]