Index, Match, Lookup HELP PLEASE!

[goodgirlinterrupted]

First I want to say I have read many posts and tried many suggestions on
those post before posting my question hoping I wouldn't have to ask a
question that has been answered several times already.

I am creating an Excel workbook for my business math class. On one page is
the actual math problems and on the other two sheets, each one has a table.
One table is Present value of $1 and the other is Future Value of a $1. When
I try and put a formula in the math problem sheet that is referenced to one
of the tables I get #NA or one of the many other error messages.

Here is an example of what I need:

Math problem is: what is the present value if in 3 years I have $15,000.00
that received 6% interest compounded quarterly.

To figure this out I have to find the compound interest and the periods of
that interest. To do this I have to take R/T or 6%/4 which is 1.5% and then
multiply the years by the compound periods 3*4 which is 12.
Once I work this out on my math sheet I need to go to the PVTable which is
my present value table and fine the periods which is 12 in the first column,
and the compound interest which is 1.5% in the first row and get the table
value where the two intersect. Because I need to be able to change the
interest and compound periods and priciple or future amounts without having
to solve all over again.

I thought it was going to be much simpler than it has proven to be but no
matter which formula I use I get an error. This was the last few I used from
other post suggestions:

=INDEX(table range including headers,MATCH(value in column,column
range,0),MATCH(row value,row range,0))

=INDEX(your range,MATCH(row value,rows to look in),MATCH(column

value,columns to look in))

=INDEX('Penalty Table'!$A$7:$F$26, MATCH($A2,'Penalty Table'!$A$7:$A$26,),
MATCH(B$1,'Penalty Table'!$A$7:$F$7,))


And several others as well. Please help me before I go crazy. I even took
an excel class two terms ago that went over all of this and I got an A, so
much for that.

Thank you
Candie

 

[Luke M]

That's an awful lot of work...why not use the single function FV?

=FV(6%/4,3,,-15000)

Note that all of these values could be replaced with cell references to make
a nice fast formula.

From XL help file:
Syntax

FV(rate,nper,pmt,pv,type)

For a more complete description of the arguments in FV and for more
information on annuity functions, see PV.

Rate is the interest rate per period.

Nper is the total number of payment periods in an annuity.

Pmt is the payment made each period; it cannot change over the life of
the annuity. Typically, pmt contains principal and interest but no other
fees or taxes. If pmt is omitted, you must include the pv argument.

Pv is the present value, or the lump-sum amount that a series of future
payments is worth right now. If pv is omitted, it is assumed to be 0 (zero),
and you must include the pmt argument.

Type is the number 0 or 1 and indicates when payments are due. If type is
omitted, it is assumed to be 0.

 

[JLatham]

It appears to me that the third example, which probably closely matches the
reality of your spreadsheet, should probably work.

BUT you need to test the two MATCH portions to see that both of them are
working properly. Just pick a couple of cells on the same sheet with that
formula and pull out the two MATCH() statements, pop an = symbol into a cell
with the MATCH() statement behind them and see which of them is or isn't
working.

It may be that you have a mismatch in the values in your label row or column.

Since the label column (number of periods) is probably integer numbers in
both cases, then it's probably not the problem. But the match of the % may
not be working because it's actually a percentage on the calculation sheet,
but maybe you're just displaying it as a decimal value in A7:F7 on the
Penalty Table sheet.

To try to explain the possible error another way: on the calculation sheet,
you've actually got a percentage, as 1.5% (or .015) while on the table sheet
you may have a row with simply numbers representing the percentages, but in
fact are whole numbers with a decimal value, as
1.5 2.0 2.25 2.5
and numbers like that won't match: 1.5 = 150% not 1.5%

 

[David Billigmeier]

Couple changes needed actually on this formula, he states $15k is the FUTURE
value, so he needs to discount to PRESENT value, so we need the present value
function: PV(). Also nper should be 12 because there are 3 years of
quarterly compounds, or 3*4, so the formula should be:

=PV(6%/4,12,,-15000)

 

[goodgirlinterrupted]

Thank you for you suggestions, but these two formula's:
FV(rate,nper,pmt,pv,type) and =PV(6%/4,12,,-15000) don't include the
compound interest that I am trying to get from my table. the 6%/4 is the
1.5% I am trying to index or look up on the row part of my table and the 12
is the periods of interest I need to look up on my column part of my table
and then where the row with 12 and the column with 1..5% intersect is the
cell with the final part of the equation which is
0.83639. So then I take my future value of $15,000*0.83639 which is
$12,545.85 which is the present value of my problem.

Does the PV or FV functions already have that interest table built into some
how and I don't need my table and I am wrong in my last paragraph? That
would be okay with me since it would be much easier you are absolutely right,
but my luck nothing is going to be that easy when it comes to this class and
assignment....lol

I will try the last suggestion I have received so far later today after
school and let you know how it goes.

Again thank you everyone and anyone who wants to still give me a sugestion
or help with this.