Excel for Finance Loan Calculator Data Table Amortization Table - PowerPoint PPT Presentation

Excel for Finance Loan Calculator Data Table Amortization Table

Financial Functions  Many financial functions – Functions for Calculating Depreciation • Straight line • Declining balance depreciation • Double declining balance • Sum of the year’s digits

Other Financial Functions  Present value  Future value  Internal rate of return  Payments

Functions we will use in this Chapter  PMT (to calculate monthly payments)  PV (to calculate the present value of a loan — a lump sum to pay it off early and avoid the interest that would accrue in the remaining years)

Three Parts of the Worksheet $ Loan Calculator on a Disk $ Data Table to Show Payments at Various Interest Rates $ Amortization Table to Show Pay off Amounts Throughout the Life of the Loan

Information Needed for the Loan Calculator  Date of the Loan  Item to be Purchased  Price  Down Payment  Interest Rate  Years of Loan

Loan Information that Excel will Calculate for us  Loan amount (after down payment is deducted from price)  Monthly payment  Total interest paid over the life of the loan  Total cost of the loan

The Data Table  Takes initial information from the loan calculator  Lists monthly payments, total interest, and total cost for several interest rates at once for comparison purposes

Amortization Table  Allows you to show the payoff amount, total interest paid, and total paid for any length of loan.  We will build an amortization table for the life of the loan.

Terminology Used in the Amortization Table  Beginning Balance  Ending Balance  Paid on Principal  Interest Paid (Definitions on following slides)

Beginning Balance  At the beginning of a loan, it is the amount of money borrowed.  For a loan that is being paid back over several years, it will become the amount owed at the beginning of a period; for example, each year.

Ending Balance  This is the amount you still owe at the end of a period, such as a year.

Paid on Principal  This is the amount of your monthly payments that actually went to reduce the principal of the loan. It is a part of the amount you paid each period toward the mortgage.

Interest Paid  This is the amount of money that went to the lender for lending you the money for the house. It is a part of the amount you paid each period toward the mortgage.