Time Value of Money

Date: 03/20/2002 at 00:12:02
From: Daniel Wagner
Subject: Time Value of Money
Dear Dr. Math,
My Dad sent me this math problom two weeks ago and I was not able to
figure it out. He told me to get some help, but no one I called or
sent it to can help. I called two engineers, one insurance company and
one Professor at a College. After looking at it for a few days the
Professor told me I needed to take it to people who actually do this
sort of thing for a living. He called it the time value of money. Can
you help? Please take a look.
Capital Invested
5-17-00 Capital investment $24,000.00
6-26-00 Capital investment $10,000.00
8-24-00 Capital investment $30,000.00
10-05-00 Capital investment $ 7,500.00
2-21-01 Capital investment $ 2,500.00
Total Capital Invested $74,000.00
Capital Returned
3-15-01 Capital returned $37,500.00
8-18-01 Capital returned $36,000.00
1-26-02 Capital returned $ 500.00
Total Capital Returned $74,000.00
Profit on Investment
1-27-02 Profit on Investment $19,735.00
Determine the annual percentage rate of yield or return on this
investment.
Daniel Wagner

Date: 03/20/2002 at 10:09:50
From: Doctor Mitteldorf
Subject: Re: Time Value of Money
Dan -
I'm surprised that none of the people you consulted could do this
calculation for you. It is not difficult in principle, as mathematical
problems go, but it requires enough calculation to be tedious without
a computer.
The principle is this: Create a "present value" function that
multiplies each payment by exp(-rt), where r is an unknown rate and
t is the date of the transaction, measured from any arbitrary date you
want to define as t=0. (The zero-point date will cancel out of your
calculation.) Investment payments count as positive amounts; returns
from investment, (it doesn't matter if they are called principal or
interest) count as negative.
Now try different values for r, using a smart trial-and-error
technique (e.g. Newton-Raphson) to home in on that value of r that
makes the function equal to zero. The r that you find is your APR,
defined as a true rate. For an APY, you can use the conversion
exp(APR)-1 = APY.
You can perform this calculation on a spreadsheet. Put your guess at a
trial r into a cell of the spreadsheet, and put in all payments as
positive or negative amounts next to their dates. In a third column,
use an @ function to calculate the present value of each payment made
on its particular date, using the trial date. At the bottom of the
present values column, calculate the total present value as the @sum
of the column.
Now try different values in the rate cell, until you make the total
present value come out equal to zero. Better yet, you can use the
spreadsheet's "solve for" capability to automate this process.
In one of my past lives, I wrote a financial calculator that was
published and distributed commercially, mostly to lawyers and
accountants. It does ROI problems, loan amortizations, APR
calculations, and mortgage comparisons. It is conveniently set up so
that you can solve for any one unknown, be it a rate or a payment or a
date, just by leaving that cell blank and filling in all the others.
The program, called Per%Sense, is now available free for download from
my personal page at the Math Forum:
http://www.mathforum.org/~josh/http://www.mathforum.org/~josh/persense.zip
Here is a screen shot from PerSense, displaying your computation:
+--DEPOSITS-------------------+
Funds | Single Payments |
IN +-Date---+--Amount---+Dollars-+
| 5/17/00| 24,000.00|CURRENT |
| 6/26/00| 10,000.00|CURRENT |
| 8/24/00| 30,000.00|CURRENT |
|10/ 5/00| 7,500.00|CURRENT |
| 2/21/01| 2,500.00|CURRENT |
+--------+-----------+--------+
+--WITHDRAWALS----------------+
Funds | Single Payments |
OUT +-Date---+--Amount---+Dollars-+
| 3/15/01| 37,500.00|CURRENT |
| 8/18/01| 36,000.00|CURRENT |
| 1/26/02| 500.00|CURRENT |
| 1/27/02| 19,735.00|CURRENT |
+--------+-----------+--------+
Interest +---------+
rate % | 24.1177|
+---------+
Settings: 360 1950
- Doctor Mitteldorf, The Math Forum
http://mathforum.org/dr.math/