Excel Formula
Posted by Mark on September 12, 2001 3:15 PM
I have a data list of loan amounts. I want to create a formula that will reduce that number to a Payment. The column is 3500 rows long so I want to just type in the formula that has a constant interest rate, a constant term then add in the loan amount Pv (Different on each row) Then have excel do the calculation changing the loan amount to a payment all the way down the column
HELP
Posted by Tom Urtis on September 12, 2001 3:33 PM
Would using the PMT function work? You could either use "hard" numbers (if the interest rate and payment periods are the same in all 3500 cases), or alternatively (and what I would recommend) entering the interest rate in one cell, the payment period in months in another cell, and referencing those cells in the formula.
Say your loan amount is in column A from A2:A3500. Enter the interest rate as a decimal (.06 for 6%) in C1, and the payment period (48 for 48 months) in cell D1. Then in cell B2 enter:
=PMT($C$1/12,$D$1,A2*-1)
Copy the formula down to B3500 or as needed.
Tom Urtis
