Life contingencies

I'm doing an excel spreadsheet for a life contingencies right now. I'm
reproducing an illustrative life table with different values for the sexes
(lx). I have completed my life table including qx,px,dx etc... so I have all
the values for px, qx, lx, and dx so far, from age 0 to like 110. I need to
calculate values for temporary life insurance and temporary life annuity due
(Ax and ax with the double dots).

The data are all aranged in columns. So what kind of excel function do you
use to get the insurance values? ( ie the sum of (v^k)*(kpx) from k=0 to n-1)
Obviously you cant sit there typing in all the values of k, so how do you get
excel to do the summation of the general formula?

Thanks

sylphide,

You really need to post a small table of example numbers, and the values that you expect. But, you
might be able to use a formula like

=SUMPRODUCT((A$2:A11^B$2:B11)*C$2:C11)

However, you might also get away with a simpler design, since you want to have a complete table.
You could calculate the value for each cell in the row, and then use a progressive sum technique.

Post your table and expected values, and then we can help more.

Also, just post once, to one group - this one, microsoft.public.excel

HTH,
Bernie
MS Excel MVP


"sylphide" <[email protected]> wrote in message
news:[email protected]...
> I'm doing an excel spreadsheet for a life contingencies right now. I'm
> reproducing an illustrative life table with different values for the sexes
> (lx). I have completed my life table including qx,px,dx etc... so I have all
> the values for px, qx, lx, and dx so far, from age 0 to like 110. I need to
> calculate values for temporary life insurance and temporary life annuity due
> (Ax and ax with the double dots).
>
> The data are all aranged in columns. So what kind of excel function do you
> use to get the insurance values? ( ie the sum of (v^k)*(kpx) from k=0 to n-1)
> Obviously you cant sit there typing in all the values of k, so how do you get
> excel to do the summation of the general formula?
>
> Thanks

Purists may not like it, but by far the easiest is to first calculate
intermediate values, the commutation functions Nx and Dx.

First calculate the table of Dx for a fixed intrest percentage (Dx = v^x*lx,
where v = 1/(1+i), i = intrestpercentage)
Then create the Nx table: N(110) = D(110), N(109) = N(110)+D(109), etc, so
sum from the end of the table to the beginning.
Now you can easily create the annuity functions: a-double-dot x n =
(N(x)-N(x+n))/D(x), etc.

To make this all variable for different mortality tables,
intrestpercentages, sexes, payments in arrear or continuous, interpolate for
broken ages and/or durations, allow for negative ages, etc, can be a lot of
work and if you use those variations within one calculation model the use of
VBA functions is almost inevitable.

But hopefully I got you started.

Don't hesitate to post again if you have questions.


--
Kind regards,

Niek Otten

"sylphide" <[email protected]> wrote in message
news:[email protected]...
> I'm doing an excel spreadsheet for a life contingencies right now. I'm
> reproducing an illustrative life table with different values for the
> sexes
> (lx). I have completed my life table including qx,px,dx etc... so I have
> all
> the values for px, qx, lx, and dx so far, from age 0 to like 110. I need
> to
> calculate values for temporary life insurance and temporary life annuity
> due
> (Ax and ax with the double dots).
>
> The data are all aranged in columns. So what kind of excel function do you
> use to get the insurance values? ( ie the sum of (v^k)*(kpx) from k=0 to
> n-1)
> Obviously you cant sit there typing in all the values of k, so how do you
> get
> excel to do the summation of the general formula?
>
> Thanks