Question

Please use Excel to answer it: You are borrowing $21,000 with no money down to buy a car. The terms of the loan call for equal monthly payments for 5 years at 4.25% annual interest. What is the amount of each monthly payment?

Answer 1

Here the formula for present value of annuity will be used for calculating annuity payments

Present value of annuity =Loan amount=Annuity payment*((1-(1/(1+i)^m))/i)

where

i-discount or intrest rate per period-4.25%/12=0.354167% per month

m-number of periods =5*12=60 (months)

Present value of annuity =Loan amount=21000

Annuity payment=Monthly payment=?

Putting values in formula

21000=Annuity payment*((1-(1/(1+.00354167)^60))/.00354167)

Solving we get

Annuity payment=Monthly payment=$389.12

Using excel

PMT-To be calculated

PV-Present value- -21000

FV-Future value-0

Type-0(End of period payments)\

Rate-intrest rate per period-.354167% per month

nper-Number of periods-60

Putting values in excel

=PMT(0.354167%,60,-21000,0,0)

Excel formula

=PMT(rate,nper,pv,fv,type)

Solving we get

Annuity payment=Monthly payment=$389.12