Excel Question: accrued compound interest in a single formula

Factual Questions
1

Not sure if I titled it correctly, but here’s what I want to do and I can’t seem to get any of the provided formulas to work right (probably because I don’t know what I’m doing :slight_smile: )

Basically, what I want is to take a single amount, set an interest rate that compounds (say, monthly), set the length of time (in years), and then have the total accrued interest. (what I really want is what the principal will grow to, but I can add two columns). This of course also assumes the monthly interest is reinvested in the principal

What I don’t want is to have to make a row/column for every single month that the interest accrues, since I want to quickly swap out interest rates and term lengths.

It would look something like this:



Principal      Int Rate     Duration     Total Accrued Int     New Balance
10,000          5.65            10               xxxx                yyyyy

And I’d simply change Int Rate or Duration and get new totals/balances

IPMT and ACCRINT and FV didn’t seem to work (or I couldn’t work them right).

Is this possible?

Thanks in advance.

2

Why couldn’t you have your month-by-month totals, but have all of the formulas refer to a single cell for the interest, and a single cell for the term? That way you could easily swap them out AND see the progression over time. Best of both worlds.

3

I would just use the compound interest formula. In your example, suppose your entries are labeled A1 through E1 left to right. Then:

E1 = (A1)(1 + (B1)/1200)^(12*C1) (assuming compounded monthly)

and

D1 = E1 - A1

Is that what you’re looking for?

4

Unfortunately Excel doesn’t have a native compound interest function (that I know of) but this should work: FV function - Microsoft Support

ETA: What Cabbage said.

5

Because I don’t care about the monthly numbers - basically, trying to show my very financially conservative fiancee how different basic (low risk, medium, high) can play out over time. Plus, if I wanted to do 20 years, that’s 360 rows to scroll through.

6

You can do it with the FV function, although there’s a slight subtlety. Assuming the sheet is arranged as Cabbage said, put E1 =FV(B1/1200, 12*C1, 0, -A1) and D1 = E1 - A1.

I’m using -A1 instead of A1 because the FV function is written in terms of cash flows instead of amounts. Since you’re spending $10000 now, it’s a net outflow and is marked as negative. The return is a net inflow and is marked positive.

7

Cool, this works, as does Cabbage’s. Thanks to both.

8

There are some other things you can do with the FV formula that would be difficult to do with straight-up compound interest. That third parameter is the monthly cashflow, so you can show the effect of making a deposit or a withdrawal every month. There’s also a fifth parameter that lets you make the deposit/withdrawal either at the end of the month or the beginning of the month, which lets you play out more scenarios. Again, watch your signs…