The Data Table feature
Data Tables allow you to swap one or more inputs through a formula to return an array of results. I’m going to walk you through three different variations for calculating loan payments:
- Using one input with five interest rates
- Using two inputs, five interest rates, and five loan lengths
- Using three inputs with five interest rates, five loan lengths, and five loan amounts
The common factor between all three is that we’ll start by calculating a loan payment in the fashion that I described in the PMT function. The PMT function will anchor all three of the Data Tables that we’ll work through.
Creating a Data Table with one input
Cells D2:D4 of the Data Table-One Input worksheet in this chapter’s example workbook are the inputs used by the =-PMT(D2/12,D3*12,D4) formula in cell D6, which returns $2,176.03 as the payment amount for a 30-year loan of $500,000 with a 3.25% interest rate. Let’s say that interest...
