Break Down Excel Formula All the Way to the Math

[DHartwig35805]

ALCON,

Greetings,

Is there a relatively simple way to break down an Excel formula to the math involved? For example, using the PMT formula, you can calculate the monthly payment for a loan, based upon interest rate, present value and length of the loan. That part is easy. What I want to know is the math behind the PMT formula, and for that matter, the math behind any formula. So, that is what prompted my question.

It sounds like "too much detail" for some, but being an old, assembler language programmer, and used to working in ones and zeros, knowing the math is almost prerequisite. However, Excel is so kind to remove the details, and just give you the facts. Well, I'd like the details.

Thanks to any who can help, and I hope to hear from someone, before the end of the year...if not sooner.

[shg]

Help shows the math behind many functions, though PMT is not among them.

[GeneralDisarray]

If you are interested in the math for the sake of the math, I'm not sure excel will give you what you want.

But, if you are interested for the math to make sense out of what arguments you need to give the formula, try this. Type =pmt( into any cell, and pause. Excel will give you a tip showing the Named arguments. If that is not helpful enough, click on the name of the function in the tip -- that will bring up the help topic for that function which will give you a lot more information.


--
Funny you mentioned that function, PMT() . I put together a loan amortization table awhile back and I used my own function to get the math right on that one. Basically, I derived it from first principle and my answer finally agreed with excel.

But, initially I was getting a funny number from excel (I wasn't dividing the interest rate by 12.... that was the whole issue). I got so annoyed, I just derived it :D

So, attached is the results I came up with. Cell C7 shows my function. And G2 is the excel version.

[MrShorty]

The help file for the PV function might have the formula. http://office.microsoft.com/en-us/ma...962.aspx?CTT=1 The 2003 help file for the PMT function points to the help file for the PV function for more information. I'm not a financials guy so I could easily be wrong, but it appears that the appropriate financials functions are solving the function described in the PV help file for the appropriate variable.

[DHartwig35805]

ALCON,

Thanks for that information. However, not sure if that will give me what I need. Guess I need an Accounting book to see how to calculate the payment on a fixed term loan.

Not that I don't appreciate all of your time though, I do appreciate it very much.

Thanks again.

[DHartwig35805]

ALCON,

Thanks for that information. However, not sure if that will give me what I need. Guess I need an Accounting book to see how to calculate the payment on a fixed term loan.

Not that I don't appreciate all of your time though, I do appreciate it very much.

Thanks again.

I know this is a repeat of an earlier post, just wanted to close the thread and not waste anyone else's time. Thanks again.

[GeneralDisarray]

see that attachment i put up on my edited post. I had this same annoying issue come up and I ended up deriving this function from scratch just to prove to myself i wasn't a douchebag.

Check the formula I labeled "Monthly P&I" you can see that it does indeed agree with the excel formula (the excel value is in the red font)