VBA Code for NPV Function with Variable interest rate

**John Vieren** · 02-02-2014, 04:12 AM

Dear all,

I would like a function which works like the NPV function except that the interest rate should be variable and a range rather than a single number.

Is it possible to see the code for the excel NPV function so that I can modify it?

Or alternatively has anyone seen such a function?

Lastly, any insights on how to create such a function would be much appreciated.

Regards John

**Decar** · 02-02-2014, 05:54 AM

I don't understand how the interest rate can be a range. You can discount cash flows (cf) with 1 discount rate. Or do you want to discount cf's each with another interest rate?

Maybe show a short calculation example and how you want it to work.

**jmac1947** · 02-02-2014, 05:55 AM

does the explanation (including formula) that is foind by following the "help on function" hyperlink not help you?

not sure what your expectations are from a "range" of interest rates are, I have always used it as a single rate

**John Vieren** · 02-02-2014, 11:10 AM

Hi I have enclosed an example.

Your help is appreciated.

Thanks John

**Decar** · 02-03-2014, 05:14 AM

First chooese the range of the interest rates and then of the cashflows.

All Credit goes to Izandol! -> http://www.excelforum.com/excel-prog...ml#post3569799

Please Login or Register  to view this content.

**John Vieren** · 02-04-2014, 01:04 AM

Hi Decar,

Thanks for the above information, I will test it out.

Regards John

**John Vieren** · 02-04-2014, 04:50 AM

Hi,

That works great...amazing !

I need to expand my formula.

In fact have 5 yield curves.

My cash flows are by year.

A column in the spreadsheet provides me with year.

So for year 1 I want to use yield curve 1, for year 2 I want to use yield curve 2, ....for year 5+ I want to use yield curve 5.

I modified the formula but am having an error and have questions as follows:

1) year as range, this is a cell do I need to use Year as cell instead?

2) the if statement is not compiling do I need to use VBA code r can i use excel functions?

Any help is appreciated, thanks JV

Please Login or Register  to view this content.

**Decar** · 02-04-2014, 05:53 AM

hmm. Maybe make another excel sheet so I understand better what you want.

As far as I understand it you could just expand the interest and cash flows columns for the years your investment is, or whatever you calculate, and plug in the interest rates and for 5+ years just double click on the site.

The problem with building the formula you described it it would only work for that calculation. If your next investment has 8 years varying yield curves you cant use it anymore.

**John Vieren** · 02-05-2014, 03:19 AM

Hi Decar,

Enclosed an example.

I did it 3 ways including:

1) Helper columns
2) Array formula
3) Your formula modified.

Actually I think i am good to go.

Thanks for your help, JV