 # NPV Calc

• I am trying to create a function to calculate the NPV based on X periods of equal cashflow.

Obviously the NPV function works as = NPV(10%,cashflow1, cashflow2, cashflow3.........) etc.

I wish the function be like so : function NPV_D (Rate, total_cashflow, periods)

Each months cash flow would be total_cashflow/term so equal.

The problem come in that the equal cashflow could range from say 6 months to 62 months.

What is the best way to do this. Am I correct in assuming that I will need to use an array ?

Any help most appreciated - even to be pointed in the right direction woul be great.

Cheers

Dave

• Re: NPV Calc

Surely you can use the PV function if your payments are fixed

i.e.

=PV(10%,5,-1000)

Will return the present value of 5 annual future payments of £1000 and would equate to the same answer as the following using NPV

=NPV(10%,1000,1000,1000,1000,1000)

Does this help ?

NB, for monthly payments, the interest rate would be divided by 12 to get a monthly rate. The years the money is paid out should be multiplied by 12 to get the correct number of payments.

• Re: NPV Calc

Yes thats great - Thanks !!!

One further question though -

What about if I wanted use unequal payments.

Ie Total cashflow = 1000

Eg in month1 = (12/78)*1000
Month 2 = (11/78)*1000
Month3 = (10/78)*1000

Using Rule of 78. This could once again be between anything from 6months to 62 months.

Once again any help appreciated.

• Re: NPV Calc

You know it's over 10 years since I heard the term "Rule of 78" :yikes:

I'm not into this stuff enough to be able to help you outright, but I find that the following site is where I go if I need to see how to o anything to do with interest rates & loans in Excel

Hopefully you will find something there that gives an example • Re: NPV Calc

Thanks for that, but it doesn't show me exactly what I want.

Essentially I need to be able to feed values into the NPV calc. How many values go into thsi calc depends on the term. So, 12 months would have 12 values. 6 montsh would only have six values etc. This is why I wondered if I would have to use an array to do this ?

Am I explaining myself properly ????

• Re: NPV Calc

Welcome to the Forum!!

Hi Dogz,

Not sure how your data is layed out, but the attachment shows how to use a Dynamic range to enter cash flows into the NPV formula.

HTH

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!