General discussion


The 'real' math behind Excel formulas

By atrav ·
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.

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Comments

Collapse -

by TheChas In reply to The 'real' math behind Ex ...

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:


Collapse -

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
fv=future value
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

Collapse -

by Senior Program Analyst In reply to

By the way the Help for "Forcast" gives you the formula its based on as well

Collapse -

by T "bananas" Bonanno In reply to The 'real' math behind Ex ...

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!


Collapse -

by douglas_A_Brown In reply to The 'real' math behind Ex ...

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]

Related Discussions

Related Forums