PMT Function when rate is set to Zero

[walkingman]

Greetings, all.

I've encountered something I'm not quite sure how to navigate.
I'm continuing with my proposal page, and, having the PMT function all in place, I realized that there are times when I will want to set the interest rate to ZERO. With one main input with a data validation list of Zero or 6%.

The wheels kinda came off when, as I calculate total cost of financing, I'm getting WILD variations from pennies to negative numbers.

I realize that when a rate is set to ZERO, there IS no interest, and amounts can be calculated with the amount financed divided by the number of payments.

What I'm looking to do is save TIME, and inputting the rate in ONE place is easier than monkeying with doing separate calculations for Zero Interest.

The idea is pretty straightforward. I either charge 6% interest or none. And I break out payments from 12 months - 60 months, based on the amount financed.

I'm using 5 cells to calc the interest with a 10% deposit (which normally draws a 6% APR) and with a 20% deposit (which provides Zero APR)...

I've rounded the elements that I'd imagine would cause the numbers to vary as they are, but I'm stumped.

Any thoughts???

I suppose I could do an IF/THEN statement to calc numbers differently. Yes, I could do that... however, I'm finding myself intrigued, as it were, to find out what the heck could be causing these variations.

Input appreciated!

String

 

[shg]

Can you give an example of a PMT formula that gives incorrect results with rate = 0?

 

[walkingman]

OK.
I'm sure the members here have experienced what I just experienced... it's all working now... haha

A quick breakdown of the challenged that started this situation, (if I may).

I inherited a spreadsheet with some proposal calculations that used a Financing Terms Matrix (table) as shown below

---------------------$0.00------ $2,001.00 --------- $4,001.00
12-------------------- 1 ------------- 1-------------------- 1
24 ------------------- 1 ------------- 1-------------------- 1
36 ---------------- #N/A ----------- 1 -------------------- 1
48 ---------------- #N/A --------- #N/A ------------------1
60 ----------------- #N/A ----------#N/A ----------------- 1


And a rounded HLookup to determine if the amount being financed qualified it for a certain number of months.

When I realized that this was about as simple as going to Mexico via Detroit, I replaced the mega-detour with nested IF statements to display (Under Minimum, Term Not Avail, or the amount of the monthly payment.

The challenge came when the row index number for the HLookup was noted on a separate worksheet... WOW, I can't imagine why that was constructed in such a way with such a small set of qualifiers.

Long and short... it works now.

I like to learn, and learning for me comes from trying it myself until I've truly reached the wall.

When I went back to grab the illustrations, I realized the Rounding functions were the culprit in the end.

A great, great lesson in working to make the simplest functions possible for the intended output.

Great to have discovered this... just needed to get some fresh perspective on the matter.

Thanks for always being willing to share expertise!

String

 

[shg]

Been there. Glad you got it sorted.