How to Calculate the Return of Insurance with Cash Value

If you have a little knowledge about insurance, you may know that when offering the same risk coverage, there are two categories of insurance products in the market. One is without cash value. The other is with cash value. For the former category, there is no cash in the account when the insurance policy is surrendered. For the latter category, there is certain cash value in the account when the insurance policy is surrendered. The policy holder needs to pay more premium every year for the latter category. The insurance company uses the extra premium to do some low to medium risk investment. So the policy holder can receive cash value when the policy is surrendered. Then you may ask, what is the annual return of this kind of investment? This figure is generally not stated in the policy illustration of the insurance product. However, we can use Excel spreadsheet to calculate this return rate in a simple way. Let’s see the example below.

Mr. Yang is 30 years old this year. After needs analysis, he understands that he needs a $200,000 coverage for critical illness insurance, till his age 65. If Mr. Yang buys an insurance policy without cash value, he will need to pay an annual premium of $1,000, till his age 65. If Mr. Yang buys an insurance policy with cash value, he will need to pay an annual premium of $3,000 for 20 years. And when his coverage ends at his age 65, he will receive a cash value of $60,000. (Simple numbers are used in this example for illustration purpose.)

We can fill the above data into Excel spreadsheet, as shown in the image above. The first column is the age of Mr. Yang, from 30 to 65. The second column is the cashflow of the insurance with cash value. Payment is in positive number, and cash back to the policy holder is in negative number. The third column is the cashflow of the insurance without cash value. Using the “minus” operation in Excel, we can easily get the difference between the second column and the third column, which is the fourth column (the cashflow difference). The next is the key step. We need to apply the internal rate of return function in Excel to the data in the fourth column, that is, IRR(D2:D37, 1). The “1” in this formula is our estimation of the internal rate of return, which is 1%. Based on the date set in our example, the result of this IRR formula is 2.692%. That is to say, while providing coverage, the net investment rate of return from the insurance company is 2.692% per annum for this product.

When we use this way to calculate the return, we have to make sure that the insurance without cash value and the insurance with cash value provide exactly the same risk coverage, so they are comparable. In addition, the two insurance products should come from the same insurance company.