Question

Hi, I need the Excel Formula for the below. Thanks much.

A business takes out a 10-year loan for $250,000 at 5.3% interest compounded monthly. What are the formulas to calculate how much interest the business will pay in the first year and how much the business will repay toward the principal? What are the resulting values? Calculate the total cost of the loan in terms of the total interest paid through the l0 years of the loan

Answer 1

Solution :- ( 5 )

(i)

Loan Amount ( PV ) = $250,000

Total Monthly Installments ( Nper ) = 10 * 12 = 120

Interest Rate per month ( Rate ) = 5.3% / 12 = 0.4417%

Now Monthly Installment ( Pmt ) =  

Formula to calculate monthly Payment = = Pmt ( Rate , Nper , PV , 0 )

Therefore Monthly installment Amount = $2,688.50

(ii)

Now in first Year total Payments Made = 12

Rate of Interest Per month = 0.4417%

Monthly Payment = $2,688.50

Now the Value of Principal Repaid in Year 1 ( PV ) =

Amount Paid towards Principal = $31,354,.52

Interest Payment in Year 1 = ( $2688.50 * 12 ) - $31,354.52 = $907.48

(iii)

Total Interest Paid in 10 Years of Loan = ( $2688.50 * 120 ) - $250,000 = $72,620

If there is any doubt please ask in comments

Thank you please rate