Calculating a deferred annuity

  • Thread starter Thread starter jtuckerjr
  • Start date Start date
J

jtuckerjr

Hello all,

I am building an Excel spreadsheet that deatils our annuit
obligations.

I need to build a formula that calculates our liability at a certai
date based on the present value of an annuity that begins payments at
date in the future.

For example:

Client gives us $2000 in a deferred annuity. Interest rate is 4.2%.
Payments of $25.50 each quarter beginning 3/29/2036 for 16 quarters.
What is the present value as of 6/30/2004?

I have tried to use the PV function but cannot get a sum that agree
with my other calculator.

Any help is greatly appreciated. Thank you!

Ji
 
Hi Jim!

I'm not sure I've interpreted your data correctly but:

=-PV(4.2%/12,127,0,PV(4.2%/4,16,25.5,0,1),0)+2000
Returns: 1757.65489642308

The present value of the annuity as at 29-Mar-2036 is:

-377.694887384173

I leave the negative sign as I think that this is your obligation to
pay. I've also assumed 4.2% is a nominal rate compounded quarterly.

We then calculate the PV of this obligation

=PV(4.2%/12,127,0,PV(4.2%/4,16,25.5,0,1),0)
Returns: 242.345103576917

But we reverse the sign of this because it is the PV of your future
obligation. And we set this off against the 2000 you have received.

My problem with interpretation is why someone would give you 2000 for
a annuity of 25.5 for 16 quarters not payable for 32 years. But my
interpretation is that you are 1757 up.
 
Back
Top