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

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

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.