s
Originally Posted by Lneal
I am trying to understand the PMT function in Excel. Here's what I don't know........
When using a loan grid such as increments of years and loan amounts, is it necessary to use absolute cell references in the PMT function? Does this make sense to anyone what I am asking?
10 yr 20yr 30yr
$210,000
$220,000
$230,000
You never HAVE to use F4 $$ absolute value references.
What that function does is make it easier to copy formulas from one cell to another (or many) without having to retype or change the formula because the cell location of some of the fixed information changes relative to the new formula location.
And who wants to do that? Might as well go back to green sheets and a Monroe calculator. (Although, I LOVED that calculator to bits.)
So just as I explained upthread, you need to figure out what is constant in the formula and lock that in.
You didn't mention an interest rate, so I'm using 0.
And I'm calculating an annual payment, not monthly
In your example, the # years in Row1 would be locked when figuring out the #payments, and the loan amount in ColumnA would be locked in for PV/principal of the loan.
So the formula would look like this in cell B2 (10yrs/$210000).
=PMT(0,B$1,$A2,0,0)
That could now be copied to the other cells and would work correctly.
If you want monthly payments, you would just use B$1*12.
You could leave the 0,0 (fv,type) at the end off if you wanted as the defaults are what you would be using anyway.
eta: I changed to formulas to reflect starting at the top corner - so the years are in row 1. Hope that doesn't/didn't confuse you.