Excel Cookbook

Chapter 10. Financial Analysis

Excel is an invaluable tool for financial analysis that helps you tackle a wide range of problems. You can use it to calculate loan payments, the projected future value of an investment, an asset’s depreciation, and estimated growth under linear, exponential, and seasonal models.

This chapter guides you through these areas and more, using functions, charts, data types, and Excel’s Forecast Sheet.

10.1 Calculating Fixed-Rate Loan Payments

Problem

You have a fixed-interest-rate loan and want to know how much you’ll pay each period.

Solution

Suppose you’re considering taking out a $5,000 loan with an annual interest rate of 10% for 12 months and want to know the monthly payments. B1 contains the interest rate (10%), B2 contains the number of months (12), and B3 contains the loan principal (5000); see Figure 10-1.

Figure 10-1. Formulas for calculating monthly loan payments

If you want to make 12 equal payments at the end of each month, you can calculate the amount of each payment using the PMT function; type =PMT(B1/12, B2, B3), which returns –$439.58. This calculation uses the formula =PMT(rate, nper, pv), where rate is the interest rate per period, nper is the total number of periods in the loan term, and pv is the principal or present value of the loan.

Note

Ensure you use the interest rate per period so that the PMT function’s rate and nper ...