PV function calculation

mse330

Active Member
Joined
Oct 18, 2007
Messages
449
Hi all,

Can someone help give me a formula that would imitate the PV function of excel. For example, if I use the below formula, then I would get an answer of $10,000

=PV(5%/12,60,188.72)

What I need is a formula that would give the same answer without using excel's built-in PV function

Thank you
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
8,787
The formula they use is here:

https://support.office.com/en-us/article/pv-function-23879d31-0e02-4321-be01-da16e8168cbd

With a little algebra, you get:

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="text-align: right;;">($10,000.41)</td><td style="text-align: right;;">0.41667%</td><td style="text-align: right;;">60</td><td style="text-align: right;;">188.72</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: right;;">-10000.4061</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">A1</th><td style="text-align:left">=PV(<font color="Blue">B1,C1,D1,E1,F1</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B1</th><td style="text-align:left">=5%/12</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">A3</th><td style="text-align:left">=-(<font color="Blue">D1*(<font color="Red">1+B1*F1</font>)*(<font color="Red">(<font color="Green">B1+1</font>)^C1-1</font>)/B1+E1</font>)/(<font color="Blue">B1+1</font>)^C1</td></tr></tbody></table></td></tr></table><br />
 

MickG

MrExcel MVP
Joined
Jan 9, 2008
Messages
14,841
Similar idea !!

=188.72/((1.004167^61)-(1.004167^60))*((1.004167^60)-1)

NB:- 1.004167 = 0.05/12+1
 

Forum statistics

Threads
1,077,856
Messages
5,336,794
Members
399,103
Latest member
PX04

Some videos you may like

This Week's Hot Topics

Top