[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1026
  • Last Modified:

Algorithm to mimic Excel PV (present value) function



I need to programatically calculate present value.  Excel has the function PV(rate,nper,pmt,fv,type) that I need reproduce exactly in javascript (I would think it would basically be the same in any language).  The parameters are :
rate: interest rate per period
nper: total # of payment periods
pmt: the payment made each period
fv: the future value, or a cash balance you want to attain after the last payment is made
type: indicate if payment is made at the beginning or end of the period

Has anyone ever had to produce this mathmatical calculation in a program that they can share the algorithm with me

If I ever needed an expert this is the time.
0
leebarnard
Asked:
leebarnard
  • 3
  • 2
1 Solution
 
Dave BrettCommented:
the Excel PV equation is

 PV*((1+ rate)^NPER)+ PMT*(1+rate*type)*(((1+ rate)^NPER)-1)/rate+FV = 0

so rearranging to solve PV

 PV = -1*(FV+ PMT*(1+rate*type)*(((1+ rate)^NPER)-1)/rate)/((1+ rate)^NPER)

Cheers

Dave
0
 
leebarnardAuthor Commented:

I could not seem to get this equation to produce the exact same results as Excel did with the same data.  I did some further searching and found this equation that seems to produce the same results as Excel (within a few pennies)

http://www.investopedia.com/articles/03/101503.asp

thanks anyway

0
 
Dave BrettCommented:
Hi,

The formula will give the same answer as it is just a rearrangement of the Excel PV formula

See this example, www.vbaexpress.com/brettdj/PV.xls. The input cells are named ranges to make it easier to read

Cheers

Dave
0
 
leebarnardAuthor Commented:

you are exactly right

thank you very much for your help
0
 
Dave BrettCommented:
Thanks for the grade
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now