# Excel Question: accrued compound interest in a single formula **URL:** \ **Category:** Factual Questions\ **Created:** [April 10, 2007, 9:25pm UTC](https://boards.straightdope.com/t/excel-question-accrued-compound-interest-in-a-single-formula/399545 "2007-04-10T21:25:16Z")\ **Posts on this page:** 8\ **Page:** 1
**Author:** ![rexnervous](https://avatars.discourse-cdn.com/v4/letter/r/e9c0ed/32.png) [@rexnervous](https://boards.straightdope.com/u/rexnervous)\ **Post date:** [April 10, 2007, 9:25pm UTC](https://boards.straightdope.com/t/excel-question-accrued-compound-interest-in-a-single-formula/399545/1 "2007-04-10T21:25:16Z")
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 🙂 ) 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: ```auto 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. ---
**Author:** ![Garfield226](https://avatars.discourse-cdn.com/v4/letter/g/9e8a1a/32.png) [@Garfield226](https://boards.straightdope.com/u/Garfield226)\ **Post date:** [April 10, 2007, 9:27pm UTC](https://boards.straightdope.com/t/excel-question-accrued-compound-interest-in-a-single-formula/399545/2 "2007-04-10T21:27:49Z")
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. ---
**Author:** ![Cabbage](https://avatars.discourse-cdn.com/v4/letter/c/f07891/32.png) [@Cabbage](https://boards.straightdope.com/u/Cabbage)\ **Post date:** [April 10, 2007, 9:42pm UTC](https://boards.straightdope.com/t/excel-question-accrued-compound-interest-in-a-single-formula/399545/3 "2007-04-10T21:42:01Z")
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? ---
**Author:** ![friedo](https://avatars.discourse-cdn.com/v4/letter/f/8edcca/32.png) [@friedo](https://boards.straightdope.com/u/friedo)\ **Post date:** [April 10, 2007, 9:42pm UTC](https://boards.straightdope.com/t/excel-question-accrued-compound-interest-in-a-single-formula/399545/4 "2007-04-10T21:42:55Z")
Unfortunately Excel doesn’t have a native compound interest function (that I know of) but this should work: [FV function - Microsoft Support](http://support.microsoft.com/kb/141695) ETA: What **Cabbage** said. ---
**Author:** ![rexnervous](https://avatars.discourse-cdn.com/v4/letter/r/e9c0ed/32.png) [@rexnervous](https://boards.straightdope.com/u/rexnervous)\ **Post date:** [April 10, 2007, 10:02pm UTC](https://boards.straightdope.com/t/excel-question-accrued-compound-interest-in-a-single-formula/399545/5 "2007-04-10T22:02:05Z")
[QUOTE=Garfield226] 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. [/QUOTE] 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. ---
**Author:** ![ultrafilter](https://avatars.discourse-cdn.com/v4/letter/u/3d9bf3/32.png) [@ultrafilter](https://boards.straightdope.com/u/ultrafilter)\ **Post date:** [April 10, 2007, 10:02pm UTC](https://boards.straightdope.com/t/excel-question-accrued-compound-interest-in-a-single-formula/399545/6 "2007-04-10T22:02:54Z")
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. ---
**Author:** ![rexnervous](https://avatars.discourse-cdn.com/v4/letter/r/e9c0ed/32.png) [@rexnervous](https://boards.straightdope.com/u/rexnervous)\ **Post date:** [April 10, 2007, 10:10pm UTC](https://boards.straightdope.com/t/excel-question-accrued-compound-interest-in-a-single-formula/399545/7 "2007-04-10T22:10:09Z")
[QUOTE=ultrafilter] 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. [/QUOTE] Cool, this works, as does **Cabbage** ’s. Thanks to both. ---
**Author:** ![ultrafilter](https://avatars.discourse-cdn.com/v4/letter/u/3d9bf3/32.png) [@ultrafilter](https://boards.straightdope.com/u/ultrafilter)\ **Post date:** [April 10, 2007, 10:15pm UTC](https://boards.straightdope.com/t/excel-question-accrued-compound-interest-in-a-single-formula/399545/8 "2007-04-10T22:15:19Z")
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…