Software

Ask something
Discussion Locked

The 'real' math behind Excel formulas

0 helpful
5 replies

by atrav 1 view

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

Is there a way, within Excel or somewhere, to find the true mathematical calculation or equation that Excel is using when the formula is entered with a function word?

For example, if I use the function formula word =AVERAGE(A1:A5), I would like to find the 'real' equation like =(A1+A2+A3+A4+A5)/5

What I'm especially interested is in the actual mathematical formula for the =PMT and =FORECAST function words.

5 replies

by thechas

If you are looking for the specific long hand formulas for financial functions, I recommend finding a financial / accounting textbook that is at least 10 years old.

Even better, would be one from the 1950's or 1960's

I don't have it right in front of me, but the payment formula is rather lengthy.

Come to think of it, if you find some old Basic books, creating an Amortization schedule was a very common programming exercise.
Calculating the payment was the first step of creating the full schedule.

The venerable HP12C financial calculator was one of the first that had an automated payment function. It is still used by bankers and real estate agents.

A PMT formula used in a program for the HP-41 is at the bottom of this page:

http://www.hpmuseum.org/software/41/41loansv.htm

Chas

by senior program analyst

I found it under the Help in Excel by looking up PMT (which referred me to PV) so look up the PV function.

PV=Present Value
PMT=payment
fv=future value
rate=rate
nper=number of payment periods
type= 0 or 1 (1 payments at beginning of period
0 payments at end of period)

Microsoft Excel solves for one financial augument in terms of the others, If rate is not 0 then:
PV*((1+Rate)^nper) + pmt(1+rate*type) * (((1+rate)^Nper) -1 )/ rate)+ FV = 0
If rate =0 then (Pmt * Nper)+PV+FV=0

From these formulas you can mathematically solve for any of the variables.

Microsoft Excel solves for one financial argument in terms of the others. If rate is not 0, then:

If rate is 0, then:

(pmt * nper) + pv + fv = 0

by t "bananas" bonanno

You can find the mathematical formulas in the Help for each function, more or less. Under PMT, it does not give the formula, but if you click "see also..." and select PV, it shows you the formulas for PV, PMT, FV, and annuities. If you look under help for FORECAST, the entire formula is listed there (ouch!) That's more math than I can handle!

Tina

by douglas_a_brown

Example a $400,000 mortgage with a rate of 5% for 30 years (360 months)
.05 / 12 = .00416

The real math
[400000*((1 + .00416) ^360) * .00416] / [(1 + .00416) ^360 -1]

This discussion is closed to new replies.