I am trying to find a formulae for a cash flow. I have in all the information
with interest rates etc but need a formulae to calculate the opening balance
required. Basically how much capital is required to fund the project with the
closing balance being nil.

On Nov 19, 6:56 pm, Kathy Winter <Kathy
wrote:
I am trying to find a formulae for a cash flow. I have in all the information
with interest rates etc but need a formulae to calculate the opening balance
required. Basically how much capital is required to fund the project with the
closing balance being nil.

Take a look at the Excel functions PV(), NPV() and XNPV() to see if
any of those are applicable.

Thanks but they aren't helping. The cash flow already incorporated interest
etc on earnings ie:

opening cash + interest - spendings = closing cash
This carries on for 50 years. Trying to determine what the opening cash will
be so that closing is zero. As interest changes with the opening cash etc. At
the moment we are using trial & error to get to closing but a formulae would
help especially when playing with scenerios.

Not sure how NPV etc would help (unless I am using these incorrectly)

"joeu2004" wrote:

On Nov 19, 6:56 pm, Kathy Winter <Kathy
wrote:
I am trying to find a formulae for a cash flow. I have in all the information
with interest rates etc but need a formulae to calculate the opening balance
required. Basically how much capital is required to fund the project with the
closing balance being nil.

Take a look at the Excel functions PV(), NPV() and XNPV() to see if
any of those are applicable.

On Nov 19, 7:35 pm, Kathy Winter
wrote:
Thanks but they aren't helping. The cash flow already incorporated interest
etc on earnings ie:

opening cash + interest - spendings = closing cash

This carries on for 50 years. Trying to determine what the opening cash will
be so that closing is zero. As interest changes with the opening cash etc. At
the moment we are using trial & error to get to closing but a formulae would
help especially when playing with scenerios.

I am tempted to suggest that you try using Excel Solver. But bear in
mind that Excel Solver is unreliable, IMHO. It often fails to find a
solution, even with a proper set up.

My understanding of your two postings is that you might have something
like the following....

D1 is the initial cash. A1 might have the interest rate for the first
period, expressed like 5% or 0.05. C1 might contain expenditures for
the first period. You neglect to mention inflows for each period; if
there are any, that might be in B1. So the "closing cash" for the
first period might be computed in D2 as:

=D1*(1+A1) + B1 - C1

Copy that down through A50:D50.

In Solver, set your target cell, D50, to Equal Value Of zero. Set
your "By Changing" cell to D1. You might also need to set up 50
constraints of the form D10. And you might need to set some Options,
for example Assume Linear Model, Precision, Tolerance and Convergence.

HTH.

PS: I often find the manual trial-and-error approach works better and
faster than trying to persuade Solver to do the right thing. I use a
"binary search" approach to the trial and error.