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

• f money borrowed.

 For a loan that is being paid back over several years, it will become the amount

• wed at the beginning of a period; for

example, each year.

Ending Balance

 This is the amount you still owe at the end

• f 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.