which financial function?

[JBoulton]

Hi, All!

Which function should be used to calculate the net cash flow effect o
a payment? If I paid $2M after tax on 9/1/98 and would have investe
instead, how much interest have I failed to collect through 12/31/03?

I've used cumipmt() and wonder if there's a more appropriate approac
especially considering the partial periods involved.

TIA,
Ji

 

[Norman Harker]

Hi Jim!

More data needed here.

The financial functions use 6 variables:

PV amount at point 0
PMT amount of regular payments per period
NPer number of periods
Interest rate per period
FV terminal value
Type payments at the beginning (0) or end (1) of each period

Solutions require 5 of these variables (even if the value of a given
variables might only be 0).

Thus far, you have provided a PV and a means of calculating NPer.

That leaves us with multiple solutions.

 

[JBoulton]

Norman,

Thanks for the reply. I thought no one was interested in this one.

PV = $2M
PMT = none
nPer = 9/98 - 12/04 in months, 76
rate = 0.06*0.66 = 0.0396 annually
FV = ??
payments at the end = 1

I actually have different yeilds for each year in question so if ther
was a way to include that I'd have a better solution.

Ji

 

[Norman Harker]

Hi Jim!

Absence of a reply in these newsgroups usually indicates something
wrong with the question. <vbg>

Assuming that your quoted rate is the APR12 (aka Nominal compounded
monthly):

=FV(3.96%/12,76,0,-2000000,1)
Returns: 2569045.08785851
The Type argument is irrelevant since there are no payments.
I use -2000000 for the initial investment (payment out)

If your quoted rate is the annual effective rate then you need to
calculate the monthly effective equivalent using:

(1+3.96%)^(1/12)-1

If rates vary over time you can use the FVSCHEDULE function:

Description:
Returns the future value of an initial principal after applying a
series of compound interest rates
Syntax:
=FVSCHEDULE(principal,schedule)

This is an Analysis ToolPak function. Note that the schedule of rates
must be effective rates for the periods in question. Help gives an
example:

=FVSCHEDULE(1,{0.09,0.11,0.1})
Returns: 1.33089
Although the usual preference would be for the array {0.09,0.11,0.1}
to be replaced by a range of cells containing the rates.

Once you have your future value, you could use that to calculate the
equivalent Payment and I suspect that this was what you meant in your
original question:

=PMT(3.96%/12,76,0,2569045.09-2000000,0)
Returns: -6600.00
i.e. The return you receive is equivalent to 6600 per month. (I used
the end of month type as I suspect from your multiplier of .66 that
this is what you should have used!)

And to check:

=FV(3.96%/12,76,6600,-2000000,0)
Returns 2000000
Thus showing the equivalence of your FV and the calculated payment.

 

[JBoulton]

Norman,

The last step for either FV() or FVSCHEDULE() would be to subtract m
beginning negative cash flow to get the cumulative opportunity cost
right?

Ji

 

[Norman Harker]

Hi Jim!

Sounds OK. Deduct or Add depends upon how you have signed the flows.
With you data that is 569045.08785851

But the trouble with this measure is that it is in absolute terms and
not much use for comparison purposes.

A better measure might be a return expressed as an annual effective
rate:

=(FV/PV)^(1/Term)-1

With your data:

=(2569045.08785851/2000000)^(1/76)-1
Returns: 0.33%

But your term is in months so this is the rate per month. To get to
annual effective use:

=(1+((2569045.08785851/2000000)^(1/76)-1))^12-1
Returns: 4.03267051542395%

Now that is much more useful for comparing with other investment
alternatives. But then you're back where you started! Because:

=(1+3.96%/12)^12-1
Returns: 4.03267051542395%