Excel 2010 Bible

Chapter 15: Creating Formulas for Financial Applications

343

Cell Formula Description

F6 =IF(C6<>””,F5+B6,””) The formula adds the payment amount to the running total.

G6 =IF(C6<>””,G5+D6,””) The formula adds the interest to the running total.

H6 =IF(C6<>””,H5-E6,””) The formula calculates the new loan balance by subtracting the

principal amount from the previous loan balance.

On the CD
This workbook is available on the companion CD-ROM. The file name is irregular payments.xlsx.

Investment Calculations ....................................................................................................

Investment calculations involve calculating interest on fixed-rate investments, such as bank savings

accounts, CDs, and annuities. You can make these interest calculations for investments that consist

of a single deposit or multiple deposits.

On the CD
The companion CD-ROM contains a workbook with all the interest calculation examples in this section. The
file is named investment calculations.xlsx.

Future value of a single deposit ...............................................................................

Many investments consist of a single deposit that earns interest over the term of the investment.

This section 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

computing interest is

Interest = Principal * Rate * Term

For example, suppose that you deposit $1,000 into a bank CD that pays a 3 percent simple annual

interest rate. After one year, the CD matures, and you withdraw your money. The bank adds $30,

and you walk away with $1,030. In this case, the interest earned is calculated by multiplying the

principal ($1,000) by the interest rate (.03) by the term (one year).

If the investment term is less than one year, the simple interest rate is adjusted accordingly, based

on the term. For example, $1,000 invested in a six-month CD that pays 3 percent simple annual

interest earns $15.00 when the CD matures. In this case, the annual interest rate multiplies by 6/12.