Microsoft Office Tutorials and References

In Depth Information

F6

=IF(C6<>””,F5+B6,””)

Adds the payment amount to the running total

G6

=IF(C6<>””,G5+D6,””) Adds the interest to the running total

Calculates the new loan balance by subtracting the principal

amount from the previous loan balance

H6

=IF(C6<>””,H5-E6,””)

Note that the formula in cell D6 assumes that the year has 365 days. A more precise formula uses the

YEARFRAC function, which calculates the fraction of a year when a leap year is involved:

=IF(C6<>””,YEARFRAC(C6,C5,1)*H5*APR,””)

This workbook is available at this book's website. The file name is irregular pay-

ments.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.

This book's website 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 de-

scribes 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% 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 (0.03) by

the term (one year).