Stormblessed
Member
Hi all, based on some friendly prompting, I've decided to come up with a guide for all those who are interested to know what sort of returns they should expect when they sign up for an insurance life policy, endowment plan, or education plan for their kids.
I've decided to start with a simple example on a 5yr NTUC Income Growth Plan.
Under this plan, you deposit a one-time amount of say $50,000, and NTUC Income will pay you back a guarenteed amount of $52,579, plus a non-guaranteed bonus of up to $5,445 giving you a total amount of $58,024.
1) What would be the actual Year-To-Year return be based on the worst-case (if non-guaranteed bonus is $0)?
2) What would be the actual Year-To-Year return be based on the best-case (if non-guaranteed bonus is $5,445)?
I've created an excel sheet which shows what I need to do to calculate:
In column A, I created a date column that shows the year for each row. Assuming I start the plan sometime this year, the growth policy will mature only in 2016.
In column B, I listed down the policy years. Since this is a single premium policy which I need to pay upfront, I start with year 0.
In column C, I listed down all the payments to & from the insurer. Payments to the insurer are reflected with a '-' sign. Payments from the insurer are reflected positively. In this example, there is a -50000 value in year 2011, followed by 0 for years 2012-2015 since there are no further payments. There is a value of 52579 in 2016 when Income pays me back the guaranteed amount.
In column D, this is where the calculations for the actual YTY return is done. If you noticed, I highlighted in yellow the formula to use which is the IRR formula. You basically apply the IRR formula on the numerical values in column C (cells C2 to C7 in my example). I get a value of 1.01% which is the YTY rate I get in the worst-case
In column E, its just similar to column C, except the last value is replaced with the best-case amount of 58024.
In column F, I apply the IRR formula again to calculate the YTY return in the best-case and get a value of 3.02%
I've decided to start with a simple example on a 5yr NTUC Income Growth Plan.
Under this plan, you deposit a one-time amount of say $50,000, and NTUC Income will pay you back a guarenteed amount of $52,579, plus a non-guaranteed bonus of up to $5,445 giving you a total amount of $58,024.
1) What would be the actual Year-To-Year return be based on the worst-case (if non-guaranteed bonus is $0)?
2) What would be the actual Year-To-Year return be based on the best-case (if non-guaranteed bonus is $5,445)?
I've created an excel sheet which shows what I need to do to calculate:
In column A, I created a date column that shows the year for each row. Assuming I start the plan sometime this year, the growth policy will mature only in 2016.
In column B, I listed down the policy years. Since this is a single premium policy which I need to pay upfront, I start with year 0.
In column C, I listed down all the payments to & from the insurer. Payments to the insurer are reflected with a '-' sign. Payments from the insurer are reflected positively. In this example, there is a -50000 value in year 2011, followed by 0 for years 2012-2015 since there are no further payments. There is a value of 52579 in 2016 when Income pays me back the guaranteed amount.
In column D, this is where the calculations for the actual YTY return is done. If you noticed, I highlighted in yellow the formula to use which is the IRR formula. You basically apply the IRR formula on the numerical values in column C (cells C2 to C7 in my example). I get a value of 1.01% which is the YTY rate I get in the worst-case
In column E, its just similar to column C, except the last value is replaced with the best-case amount of 58024.
In column F, I apply the IRR formula again to calculate the YTY return in the best-case and get a value of 3.02%