Finance Equation Setup

go.go

New Member
Joined
Mar 11, 2009
Messages
10
Hello all, I am having a problem with creating an equation in excel that would return a PV factor such as in the following example:

(Input)
Discount rate: 6%
Study period:30 years
Maintenance Frequency: Every 7 years

(Output)
PV factor

How can I setup an equation that would calculate a PV factor of the maintenance frequency within 30 years so that excel will recognize that there are only 4 maintenance periods which are during the 7th, 14th, 21st and 28th years?

What I mean is instead of having to manually type this:

(1+0.06)^-7 + (1+0.06)^-14 + (1+0.06)^-21 + (1+0.06)^-28

Thanks in advance guys!
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

c_m

Well-known Member
Joined
May 29, 2008
Messages
836
go.go,
Welcome to the Board.

Try:
<title>Excel Jeanie HTML</title><table style="padding-right: 2pt; padding-left: 2pt; font-size: 10pt; font-family: Arial,Arial; background-color: rgb(255, 255, 255);" border="1" cellpadding="0" cellspacing="0"> <colgroup> <col style="font-weight: bold; width: 30px;"> <col style="width: 69px;"> <col style="width: 64px;"> <col style="width: 64px;"></colgroup> <tbody> <tr style="font-weight: bold; font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;"> <td> </td> <td>A</td> <td>B</td> <td>C</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">1</td> <td>Rate</td> <td style="text-align: right;">6%</td> <td> </td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">2</td> <td>Period</td> <td style="text-align: right;">30</td> <td>years</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">3</td> <td>Freq</td> <td style="text-align: right;">7</td> <td>years</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">4</td> <td> </td> <td> </td> <td> </td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">5</td> <td>PV Factor</td> <td style="text-align: right;">1.597144</td> <td> </td></tr></tbody></table>
<table style="border-style: groove; border-color: rgb(0, 255, 0); font-size: 10pt; color: rgb(0, 0, 0); font-family: Arial; background-color: rgb(255, 252, 249);"> <tbody> <tr> <td>Spreadsheet Formulas</td></tr> <tr> <td> <table style="font-size: 9pt; font-family: Arial;" border="1" cellpadding="2" cellspacing="0"> <tbody> <tr style="font-size: 10pt; background-color: rgb(202, 202, 202);"> <td>Cell</td> <td>Formula</td></tr> <tr> <td>B5</td> <td>{=SUM((1+B1)^(-B3*ROW(INDIRECT("1:"&INT(B2/B3)))))}</td></tr></tbody></table></td></tr> <tr> <td>Formula Array:
Produce enclosing
{ } by entering
formula with CTRL+SHIFT+ENTER!
</td></tr></tbody></table>
 

go.go

New Member
Joined
Mar 11, 2009
Messages
10
c_m, you are my savior! :pray:Thanks alot for your help!

Sorry if im asking too much, Im very new with excel and currently in the process of learning it. I would appreciate if you can explain your formula method to me. For the most part I understand, up until the ROW part and what does Forumla Array exactly do? Everything is working now, thanks again!
 

c_m

Well-known Member
Joined
May 29, 2008
Messages
836
go.go,

You can actually do that without pressing control+shift+enter by using this formula:
=SUMPRODUCT((1+B1)^(-B3*ROW(INDIRECT("1:"&INT(B2/B3)))))
with just enter (which makes it a non-array formula).

If you would like to know more about array formula, see this:
http://www.cpearson.com/Excel/ArrayFormulas.aspx
and for row & indirect function, see this:
http://www.cpearson.com/Excel/indirect.htm

Also, try to evaluate the formula step by step by using Tools>>Formula Auditing>>Evaluate Formula

If it still doesnt help, post back and I will explain.
 

go.go

New Member
Joined
Mar 11, 2009
Messages
10
Thank you again c_m! That forumla evaluation function took care of everything! =)
 

Watch MrExcel Video

Forum statistics

Threads
1,129,800
Messages
5,638,430
Members
417,025
Latest member
MusterDuster

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top