Question

Jimmy Herring can borrow $100,000 for three years at 8.0% interest with monthly payments based on a 30-year amortization. Assume twelve 30-day months in a year, payments always made on the first day of the month and monthly accrual of interest. Create an interactive amortization schedule in Excel that contains an input menu and that shows monthly payments, monthly interest, monthly principal and the ending monthly balance in columns. Beginning and ending dates do not matter at this point – just number the 36 months. The amortization schedule should survive changes to the input menu, meaning that a change to the loan amount should result in the amortization schedule and all elements being updated automatically.

Functions/Skill Sets for this Assignment – In Part 1 of this assignment you learned how to • Create an interactive input menu that calculates a payment amount from an initial loan amount, fixed interest rate, and an amortization basis. • Create an amortization schedule that updates when the variables in the input menu are changed. • Use Absolute, Relative and Mixed References in model design to avoid repetitive coding

*I ONLY NEED TO KNOW HOW TO CALCULATE THE PAYMENT AMOUNT IN EXCEL**

Answer 1

PVAnnuity Due = c*((1-(1+ i/(f*100))^(-n*f))/i)*(1 + i/(f*100))

C = Cash flow per period

i = interest rate

n = number of payments I f = frequency of payment

100000= Cash Flow*((1-(1+ 8/1200)^(-30*12))/(8/1200))*(1+8/1200)

Cash Flow = 728.91

Using Calculator : Press buttons : "2ND"+"PMT"+"2ND"+"ENTER"+"2ND"+"CPT" then assign

PV =-100000

I/Y =8/12

N =30*12

FV = 0

CPT PMT

Using Excel

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

=PMT(8/(12*100),12*30,,100000,1)