Microsoft Excel: Calculate Many Scenarios For Loan Payments

See all Microsoft Excel tips

Strategy: Follow the same setup that you used for the previous tip, as shown in Fig. 327.

1) Enter Price, Term in Months, and Annual Percentage Rate in cells B2:B4. See Fig. 327.

2) Enter this formula in cell B6: =–PMT(B4/12,B3,B2). See Fig. 327.

3) Copy cells B2:B6 over for several columns. Plug in different numbers for the price and/or term, as shown in Fig. 328.

Summary: This is where Excel shines. After you have entered the formulas for one loan model, you can easily copy and create many more

loan models.

Functions Discussed: =PMT()

Cross Reference: Calculate a Loan Payment

See all Microsoft Excel tips