Has anyone got a good way to make these calculations

[Mike375]

An insurance policy can be purchased with two premium styles. One is where the premium remains the same each year and the other is where the premium increases each year BUT for the first few years it will be lower than the level or fixed premium. Would look like

5000 3000
5000 3300
5000 3700
5000 4200
5000 4800
5000 5300
5000 5900
5000 6600
5000 7300

And so on. An assumption is made that the buyer can outlay a maximum of $5000 per year. So the buyer of the increasing premium pays 3000 in the first year which means he has 2000 left to invest and obviously the amount available to invest each year goes down.

In the above example the increasing premium has reached 5300 by year 6 which is 300 more than he can pay. So he pays 5000 and withdraws 300 from the investment account and each year the amount withdrawn from the investment account will increase so as to top up his $5000

What I need to do is determine the interest rate necessary for the person with the increasing premium to be able to maintain the policy for a specified number of years such as 20 years etc.

In other words if the interest rate was 7% then at the end of 20 years his investment account would be reduced to 0. But if the interest was only 4% then the investment account might be 0 by only 12 years or whatever.

The point where the increasing premium exceeds the level or fixed premium is variable but generally between 6 to 10 years.

I could probably make something whereby the number of years before the increasing premium exceeds the fixed could be done and then apply a financial function to each years investment for the number of years.

Or perhaps do an automated manual calculation where each year the invesrtment is multiplies by 1.07 or whatever and an IF when it gets to the record where Fixed - Increasing is a negative.

 

[Mike375]

PS.

It can be a trial and error situation. That is an interest rate is selected but is too high or too low.

 

[gemma-the-husky]

easier to do this in excel

set up the spreadsheet with the calculations, then use the solver facility to find the critical value

the final result, and a critical cell will be related, and solver will find one based on the other

 

[Mike375]

I need to do it in Access.

 

[gemma-the-husky]

i would still do it in excel first, to clarify how to do it - then emulate this in access

 

[gemma-the-husky]

mike

i thought i would have a look, but your second column appears to vary by arbitrary amounts - surely there has to be a formula for the payments in the second column.

and i still dont quite see how the investments get reabsorbed

can you explain again

 

[Mike375]

I have just made a rough version in Access.

Fields

FixedPrem...IncreasePrem...Diff....Value...Store...IntRate (unbound

Interest rate as in 1.08 etc goes in IntRate

A little SetVlaue macro puts ([IntRate]*[Diff])+[Store] into Value and then copies Value to Store in the next record and down the records it goes.

Thus when it goes down to the records where the IncreaseRate is higher than the Fixed then ([IntRate]*[Diff]) is a minus and is added to [Store]

 

[Mike375]

The increasin rates are known. I just made thiose numbers up although they are roughly what it would be like. It will vary with age and policy type.

So in year one the man with the fixed premium pays his $5000 and the other bloke pays his $3000 and so has $2000 to invest.

We will assume yearly premium and investment in advance and interest of 10%

So we come to the 2 nd year and the bloke with the fixed premium pays his $5000. The fellow with the increasing premium pays $3300. His investment of $2000 has grown to $2200 and he had another $1700 to invest (the 5000-3300) so his investment is now $3900.

Get to the next year and the other bloke pays his $5000. The increasing premium rate is now $3700 so he has $1300 left to invest. His $3900 invesment account has grown to 4290 (1.1 X 3900) and with the $1300 left to invest it goes from 4290 to $5590

As we move a bit further along the increasing premium rate will be greater than the $5000 and $5000 has been allocated as the maximum either bloke can pay. So if the increasing premium had grown to $6000 then he can only pay $5000 so gets the other $1000 by withdrawing from his investment account.

 

[Mike375]

Attached roughy

Open the form and click on yellow text box on first record. It runs Macro1 which is a RunMacro for Macro2

At the moment I just have the interest rate in the macro.

Edit: Ignore I put the wrong version up.

 

[Steve R.]

I'm thinking about it. I don't know if I can really come-up with something. I realize that the numbers are "hypothetical", but it leads me to two quick observations.

At a minimum, both types of policies, should result in an equal total payment by the policy holder to the insurance company.

However, there is a "problem" with the variable rate. True, the policy holder could invest the difference between the fixed and variable rates. The problem is that when the policy holder pays a reduced rate, it deprives the insurance company of an investment opportunity. To adjust for this, I would assume that the insurance company would require that variable payment rate plan, in the end, would result in a higher total payment to the insurance company.

A flat(fixed) payment of $5,000 per year would result in a total payment of $50,000. A variable payment plan, that takes into account "lost" investment opportunities for the insurance company, could probably result in a hypothetical total payment of $52,000.

 

[pkoko]

Let me ask a simple question: What do you gain exactly from doing this in Access? Are you storing the results in some sort of Database? If not, then you should be using excel.

If you still want to do it in access I would use the financial built in functions to do an IRR (Internal Rate of Return) calculation.

 

[Mike375]

The attached roughy woerks OK for the basic numbers. Open form and enter interest as 1.08 or 1.12 etc format and click the blue label. The fied on the far right is only storing for each year and the yellow field is the investment value.

 

[Mike375]

At a minimum, both types of policies, should result in an equal total payment by the policy holder to the insurance company.

They don't. Over the distance total premiums paid are much higher with the increasing premium. There are two basic reasons. One is that premiums that are fixed or level as opposed to increasing with age stay on the books longer. Basically the premium each year for the age increasing policy is about the same if a new policy was purhased.

Secondly the insurance company gets more money earlier. Third, the insurance company actually charges a lower raw premium for the level premium policy.

It also depends on the age at entry. For older ages, say 50 and more things are different. One reason is that the older person is less likely to switch companies for a cheaper step rate due to medical issues, just the pain of going through it. Thus for the older person the insurance company does not gain much in the way of the level premium policy staying on the books longer.

 

[Mike375]

Let me ask a simple question: What do you gain exactly from doing this in Access? Are you storing the results in some sort of Database? If not, then you should be using excel.

If you still want to do it in access I would use the financial built in functions to do an IRR (Internal Rate of Return) calculation.

A couple of reasons for Access. For one I am much more familiar with it than Excel. Two, I did make one of these a few years ago in Access so knew it could be done but I had forgotten hw I did it.

And most importantly it needs to reside in an existing main data base.

 

[Mike375]

Younhave to invst the difference at a very attractive rate. in addition it would need to be tax free and guaranteed.

On the fixed premium the insurer also invests the difference and in fact common practice is the insurance company charges a raw premium each month. But because the raw rate is lower for the level premium the insurer actually has more to invest than does the person who buys stepped premium and invests the difference each year.