Finance Equation Setup

[go.go]

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!

 

[c_m]

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]

c_m, you are my savior! ray: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]

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]

Thank you again c_m! That forumla evaluation function took care of everything! =)