# Finance Equation Setup

#### go.go

##### New Member
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

### Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
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>

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!

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.

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

Replies
2
Views
410
Replies
1
Views
450
Replies
7
Views
161
Replies
0
Views
177
Replies
8
Views
342

1,203,751
Messages
6,057,147
Members
444,908
Latest member
Jayrey

### 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.

### Which adblocker are you using?

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

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