calcuation of an investment payout

  • Thread starter Thread starter scott
  • Start date Start date
S

scott

I am trying to calculate the payout from an investment
over a 20 year period in Excel. I am assuming a lump sum
start value and a constant rate of interest. The end
value of the investment would be zero. I have been trying
to use several of the finacial equations but nothing
works. Any ideas? Thanks.
 
Hi Scott!

Use the PMT function:

=PMT(rate,nper,pv,fv,type)
Returns the periodic payment for an annuity

If you invested 100000 and enjoyed a return of 5% the annuity received
would be:

=PMT(5%,20,-100000,0,0)
Returns: 8024.25871906913

I suspect that your problem with the financial formulas is that you
are not using the signage of money flows conventions. The initial
investment in this case is given the negative sign and the return is
positive. Another problem may be that you are not matching the
interest rate, number of payments and payment per period. If the
interest rate is an effective annual rate then the number of periods
must be years and the payments will be annual.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Back
Top