The Payment calculation in OneNote

One of the testers on our team came by and had been tasked with verifying our Payment command is working fine.

It is, but the tester was getting numbers that did not make sense.

First, a quick tutorial on that function, which in our lingo is called a "napkin math" function.

It works like this if you have an interest rate, a number of payments and the amount you want to borrow.

Suppose you want to borrow $1000 for 36 payments at 8% interest.

The formula looks like this:

pmt( interest rate ; number of months ; amount to borrow) = and then hitting enter or space will solve the equation.

(As a side note, I always seem to forget to use ; in the equation).

Here's what it would look like:

pmt(0.08; 36 ; 1000)=85.3446740768714

And that means you would wind up paying about $3072 over three years for that $1000 today:

85.34*36=3,072.24

But the numbers the tester were getting were extremely large. Her copy of OneNote was telling her that the monthly payment would be about $8000!

I doubted there was a bug since this test is automated and passing, so a quick look revealed this formula:

pmt(8;36;1000)=8000.0

Notice the interest rate here is "8" as opposed to the "0.08" I used up top? That's the key. The interest rate Oneote uses should be between 0 and 1. .08 for 8%, .05 for 5%, .169 for 16.9%, etc…

This formula is documented in our online help here and I thought a little explanation may help.

Questions, comments, concerns and criticisms always welcome,

John

Comments

• Anonymous
  December 17, 2012
  Do comments work? I just spent 30 minutes on a previous comment, hit post, and it disappeared. No message about pending review, nothing. I tried using the back arrow in the browser, but wasn't able to recover. Very sad....

• Anonymous
  December 17, 2012
  This one came through and I got email on it.  I don't know what might have been happening earlier.