Issue understanding the use of Cashflow

[van...@gmail.com]

I've been tearing my hair out (what little I have left) trying to understand what I'm missing in the difference between using Cashflow in TimeValue and Annuity, any insight would be appreciated.

If I take a 250K loan and use Solve and TimeValue to find the payment for 30 years at 5 percent I get 1342.05 and if I plug this in to TimeValue[Annuity ....} all the amortization tables and such work out fine and as expected.

However, what I am trying to do is to work out the ability to provide a cash flow stream that has varying payments and to calculate the remaining value of the loan a various points in time.

What I am seeing is that the loan seems to pay off way too fast in the Cashflow case. Here are some specifics:

Here are the first 10 elements of the Cashflow specification (there are 360 in all)

In[350]:= testCashFlowSpecification[[1 ;; 10]]
Out[350]= {{{2012, 2, 1, 0, 0, 0}, -250000.}, {{2012, 3, 1, 0, 0, 0},
1342.05}, {{2012, 4, 1, 0, 0, 0}, 1342.05}, {{2012, 5, 1, 0, 0, 0},
1342.05}, {{2012, 6, 1, 0, 0, 0}, 1342.05}, {{2012, 7, 1, 0, 0, 0},
1342.05}, {{2012, 8, 1, 0, 0, 0}, 1342.05}, {{2012, 9, 1, 0, 0, 0},
1342.05}, {{2012, 10, 1, 0, 0, 0},
1342.05}, {{2012, 11, 1, 0, 0, 0}, 1342.05}}

My thinking has it that if I look at the TimeValue of these 10 elements at {2012,12,1} I should get the same value as:

In[351]:= TimeValue[Annuity[1342.05, 360 - 10], 5./1200]
Out[351]= 246938.

If I do:

In[354]:= TimeValue[Cashflow[testCashFlowSpecification[[1 ;; 10]]],
5./1200, {2012, 12, 1}]
Out[354]= -238765.

But the result is dramatically different with the CashFlow version amortizing way faster than expected (and not just by an amount that might be associated with one payment or start of month/end of month type issues).

Any support in what I am missing here would be much appreciated.

[van...@gmail.com]

If anyone is looking at this, it seems that the issue has something to do with the way that specifying time as Date is handled. If you use numeric periods everything works out as expected.

In[414]:= TimeValue[Cashflow[testCashFlowSpecification[[1 ;; 10]]],
5./1200, 10]

TimeValue[Annuity[1342.05, 360 - 9], 5./1200]

Out[414]= -247251.

Out[415]= 247250.

Either I don't understand how to use the date format, which is quite possible, (and there isn't much documentation on it) or there is something wrong with the function...

[Stuart Nettleton]

Hi, you might have missed the cashflow at time zero 0. These two
formulations give the same answer of 246,938:

npv1=TimeValue[Annuity[1342.05, 360 - 10], 0.05/12]

npv2=TimeValue[Cashflow[Join[{0}, Table[1342.05, {i, 360 - 10}]]], 0.05/12]

Also, you can check this with -NPV as the time zero element:

npv3=TimeValue[Cashflow[Join[{-npv1}, Table[1342.05, {i, 360 - 10}]]],
0.05/12]

and you have Chop[npv3] == 0

For and amortization schedule, look at the help page for TimeValue, which
provides a clear example:
"Since a loan's balance at any time is equal to the present value of its
remaining future payments, Annuity can be used to create an amortization
table"

Cheers, Stuart

UTS CRICOS Provider Code: 00099F
DISCLAIMER: This email message and any accompanying attachments may contain confidential information.
If you are not the intended recipient, do not read, use, disseminate, distribute or copy this message or
attachments. If you have received this message in error, please notify the sender immediately and delete
this message. Any views expressed in this message are those of the individual sender, except where the
sender expressly, and with authority, states them to be the views of the University of Technology Sydney.
Before opening any attachments, please check them for viruses and defects.

Think. Green. Do.

Please consider the environment before printing this email.