PC help: Compound problem

Q I am using an Excel spreadsheet to list various Peps and Isas enabling me to revalue my portfolio on a regular basis. If I have held a Pep for, say, 10 years, then the growth per year is 10 per cent per year, leading to a total growth of 100 per cent over the 10 years.

However, this is simple interest, but I am hoping to find a formula so I can see the compound rather than simple interest per year, which would be far more realistic. Can you tell me how to do it?
Colin Mott

A You’re right, the simple interest you’re modelling isn’t very realistic, as it assumes that you only get paid interest on the initial amount in the account. Compound interest, in which interest is paid on the current balance, including last year’s interest, is what is used by financial institutions.

There’s no easy formula for this, but it’s possible to do it by hand fairly simply. For instance, if your interest rate is shown in cell B1 and the initial investment is shown in B2, the formula for the interest after the first year is =B2+((B1/100)*B2) for both simple and compound interest.

For subsequent years you need to write a similar formula for each subsequent cell, where the B1 stays the same, but both B2 entries in the formula are replaced by the cell containing the previous year’s figure.

That can be quite hard to mentally picture so we’ve uploaded a sample spreadsheet ­ you can use this as a starting point for your own calculations, or simply click a particular cell to see the formula used to calculate it.

Like this story? Spread the news by clicking below:

 del.icio.us     Digg this     reddit!