Page 346 - Excel 2007 Bible

21_044039 ch16.qxp 11/21/06 11:07 AM Page 303
Creating Formulas for Financial Applications
TABLE 16.3
Formulas to Calculate a Loan with Irregular Payments
Description
Formula
Cell
The formula calculates the interest, based on the payment
D6
=IF(C6<>””,(C6-C5)/365*H5*APR,””)
date.
E6
The formula subtracts the interest amount from the
=IF(C6<>””,B6-D6,””)
payment to calculate the amount credited to principal.
F6
The formula adds the payment amount to the running
=IF(C6<>””,F5+B6,””)
total.
The formula adds the interest to the running total.
G6
=IF(C6<>””,G5+D6,””)
The formula calculates the new loan balance by
H6
=IF(C6<>””,H5-E6,””)
subtracting the principal amount from the previous loan
balance.
This workbook is available on the companion CD-ROM. The file name is irregular 16
ON the CD-ROM payments.xlsx.
ON the CD-ROM
Investment Calculations
Investment calculations involve calculating interest on fixed-rate investments, such as bank savings
accounts, Certificates of Deposit (CDs), and annuities. You can make these interest calculations for invest-
ments that consist of a single deposit or multiple deposits.
The companion CD-ROM contains a workbook with all of the interest calculation examples in
ON the CD-ROM this section. The file is named investment calculations.xlsx.
ON the CD-ROM
Future value of a single deposit
Many investments consist of a single deposit that earns interest over the term of the investment. This sec-
tion describes calculations for simple interest and compound interest.
Calculating simple interest
Simple interest refers to the fact that interest payments are not compounded. The basic formula for comput-
ing interest is
Interest = Principal * Rate * Term
For example, suppose that you deposit $1,000 into a bank CD that pays a 5 percent simple annual interest
rate. After one year, the CD matures, and you withdraw your money. The bank adds $50, and you walk
away with $1,050. In this case, the interest earned is calculated by multiplying the principal ($1,000) by
the interest rate (.05) by the term (one year).
303