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


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


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.

Screenshot of formulas
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.


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

Get Excel Cookbook now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.