Calc: PPMT() not working

[chuckorwc]

I’m trying to do some simple (I thought) mortgage calculations in Calc. Specifically, I want to compare various loans at 5 years into the loan. I’m using PPMT() to accomplish this, but I keep getting a 0 value no matter what I put in. For example, PPMT(D4/12, 60, C4*12, $B$1) yields $0.00. Even when I replace all cell references with actual numbers, it returns $0.00. The PPMT() function appears to be broken.

Interestingly, calc also doesn’t work when I try using an amortization formula. E.g., the formula

=$B$1*(((1+(D4/12))^(C4*12))-(1+(D4/12))^60)/(((1+(D4/12))^(C4*12))-1)

returns the total loan amount ($B$1)… Essentially everything after the first parentheses evaluates as 1, even though it should evaluate to a number between 0 and 1.

Any ideas?

Latest version (7.3.4.2) of LO installed on Windows 11.

[Zizi64]

Please upload an ODF type sample file here.

[mikekaganski]

Works just fine.

---

Did that help?
I bet no. Why? Because you can’t see from my “informative” message, what I tried, what is different with your data, and so where’s the problem.

And I also couldn’t tell what was your input, and so couldn’t tell what was the problem. $B$1? D4? C4? They are addresses into a spreadsheet on @chuckorwc’ system; and strangely enough, I couldn’t check what was there, sitting in front of my box.

Had you put a too large value into C4? Say, 480? That would be too many years, you know. As if you forgot that you convert years into months in the formulas. Together with yearly rate like 5% (or greater) in D4 (and large enough value in B1), that would give you 0 as PPMT result, and B1 as your “amortization formula” result. Or did you forget to add % to the value in D4 - putting some “small” value like plain 1? You can invent other combinations giving you such results; these were just random numbers that I tried to get ~what you report.

Please provide enough information for the people who you hope would spend their time answering you.

[chuckorwc]

Thank you Mike, and sorry for not providing enough info. Yes, it did help. All the values were correct, except the APR value in D4. I did not realize that cell had to be formatted as a percentage. Both the PPMT() function and the amortization formula now work.

[mikekaganski]

@chuckorwc

cell had to be formatted as a percentage

A small clarification: the rate cell does not need to be formatted in any special way; what is needed is that it contain a value that represent a part of a whole. I.e., if you have a rate of 10%, that is equal to 1/10th part of a whole; if your rate is 5%, it is 1/20th = 0.05. You may enter such 0.05 into the cell directly; or you may use the percentage notation (adding % to a number), keeping in mind that in Calc, the appended % means “multiply by 0.01” (or “divide by 100”). I.e., 5% = 5 * 0.01 = 0.05.