Originally Posted by Lneal
=-PMT($E$5/$E$6, D$9*12, $C11) this made it correct
=-PMT(E$5/E6, $D$9*E6, $C10).
Why are the answers correct?
Because the formulas are correct. Not pretty, but correct.
-The rate is calculating off the same cells even though the $$ are different,
-The #payments is also calc correctly even though you have 12 in one and E6 in the other. As the value in E6 is 12.......see?
-And as you're referencing the correct PV/loan amounts, each formula would calculate correctly - C10 and C11.
A little tip:
Sometimes, with convoluted formulas - and this isn't even half bad - I will go to the cell with the forumla, EDIT and put a ' in front to make it a label. That way I can really study it, instead of looking up at the bar on top. To make it a formula again, just delete the '.
Try this formula:
=-PMT($E$5/$E$6,D$9*12,$C11)
This should copy correctly.
I'm assuming row 9 has the #years.
The difference to your first one is that you locked in BOTH the column and the row to figure your rate, but not in the second.
So if you copied the first one, you'd be ok because I'm assuming that's the ONLY place you the 6.5% and 12.
However, if you copy your 2nd one, that formula is going to shift depending on where you move it.
So since the data only appears in those 2 cell on your spreadsheet, you have to LOCK those locations.
And I don't mind helping, but all that information in your second post would have been helpful in the first post.
Saves everyone time. ;-) :thumbup: