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/