Churchill University is offering its faculty a generous retirement package. Professor Norman Smith has accepted the proposal; but before moving to his retirement home in Naples, Florida, he wants to settle his account with the school credit union. Professor Smith has five years remaining on a six-year car loan, with an interest rate of 8.25 percent and a monthly payment of $525.00. The credit union is willing to accept the present value (PV) of the loan as payoff.
Develop an amortization schedule that shows how much Professor Smith must pay at the end of each of the six years. You may use the Excel Help files to learn more about the present value. Design and create a worksheet that includes the beginning and ending balance, the amount paid on the principal, and the interest paid for years two through six. Because he has paid for the first year already, determine only the ending balance or the present value for one year.
Use PV function in Excel.
=PV(Rate,Nper,Pmt)
Rate/Month =8.25%/12
Pmt/Month =525
No of Months=12*5
So =PV(8.25%/12,12*5,-525) =$25,740.02
Now make an amortization table like this,
Month 1—-
Beginning balance =$25,740.02
Interest Due =(Beginning Balance *Rate/Month)
Installment = 525
Capital Repayment = 525-Interest due
Closing Balance =Beginning Balance – Capital Repayment
Month 2—–
Beginning balance = closing balance of Month 1
Interest Due =(Beginning Balance *Rate/Month)
Installment = 525
Capital Repayment = 525-Interest due
Closing Balance =Beginning Balance – Capital Repayment
Do this for all 60 months. By the end of 60th month, your closing balance should be zero.
(It is better to put above line descriptions horizontally and months vertically —like this http://images.google.com/imgres?imgurl=http://www.free-mortgage-calculator-software.com/images/amortization_table1.gif&imgrefurl=http://www.free-mortgage-calculator-software.com/View_Amortization_Table.html&usg=__5Hl_pxYcID_7N5oaAbKHbuX3wBM=&h=319&w=700&sz=8&hl=en&start=1&um=1&tbnid=2t0BJQPV4NE5_M:&tbnh=64&tbnw=140&prev=/images%3Fq%3Damortization%2Btable%26hl%3Den%26sa%3DN%26um%3D1)
If you need more info, e-mail me.
Use PV function in Excel.
=PV(Rate,Nper,Pmt)
Rate/Month =8.25%/12
Pmt/Month =525
No of Months=12*5
So =PV(8.25%/12,12*5,-525) =$25,740.02
Now make an amortization table like this,
Month 1—-
Beginning balance =$25,740.02
Interest Due =(Beginning Balance *Rate/Month)
Installment = 525
Capital Repayment = 525-Interest due
Closing Balance =Beginning Balance – Capital Repayment
Month 2—–
Beginning balance = closing balance of Month 1
Interest Due =(Beginning Balance *Rate/Month)
Installment = 525
Capital Repayment = 525-Interest due
Closing Balance =Beginning Balance – Capital Repayment
Do this for all 60 months. By the end of 60th month, your closing balance should be zero.
(It is better to put above line descriptions horizontally and months vertically —like this http://images.google.com/imgres?imgurl=http://www.free-mortgage-calculator-software.com/images/amortization_table1.gif&imgrefurl=http://www.free-mortgage-calculator-software.com/View_Amortization_Table.html&usg=__5Hl_pxYcID_7N5oaAbKHbuX3wBM=&h=319&w=700&sz=8&hl=en&start=1&um=1&tbnid=2t0BJQPV4NE5_M:&tbnh=64&tbnw=140&prev=/images%3Fq%3Damortization%2Btable%26hl%3Den%26sa%3DN%26um%3D1)
If you need more info, e-mail me.
References :