Excel Formula for compound interest for monthly deposits where deposits increase every year

0 votes

Simply expressed, I want to know how to access the Excel capability found on this page.

I attempted to use the FV function, but it does not appear to have support for annual payment increases.

The payment increase may be expressed in percentage points (as in the linked page) or even by a set sum each year (like "increase deposit by 10000 every year).

An illustration:
Year 1:

Monthly Deposit: 5000
No. of deposits in a year: 12
Interest rate: 10%
Interest compounded quarterly
Total deposited during the year: **60000**
Interest earned in first year: **3323**

Year 2:

Amount carried forward from first year: **603323** (principal + Interest)
Monthly Deposit: 5500 (increased by 10% or increased by a fixed value of 500 every year)
No. of deposits in a year: 12
Interest rate: 10%
Interest compounded quarterly
Total deposited during the year: 66000
Interest earned during year: 13552 (large, because we started the year with seed value from previous year)

and so on.

The FV function gives me the correct value for year 1, but I could not find a way to extrapolate it to increase investment every year.

Nov 26, 2022 in Others by Kithuzzz
• 38,020 points
2,591 views

1 answer to this question.

0 votes

It's possible that I don't understand how to calculate quarterly interest because I'm using Excel 2003. You should be able to adjust the monthly compound solution I have. To add further years, enter the year number in column A and copy and paste the formula into column B. The formula is =FV($B$2/12, $B$3, $B$4, +A9*$B$7, -$B9,$B$6) in cells b10. It increases the value in B7 to the payment rate for every additional year, using the previous year's outcome as the baseline.

Screenshot

answered Nov 26, 2022 by narikkadan
• 63,720 points

Related Questions In Others

0 votes
1 answer

Excel - compound interest with continuing contribution

The FV() formula would be what you're looking for. =FV(10%,35,-10000) There ...READ MORE

answered Oct 8, 2022 in Others by narikkadan
• 63,720 points
698 views
0 votes
1 answer

Excel formula for Euclidean distance

Euclidean Norm of a vector of size ...READ MORE

answered Oct 9, 2022 in Others by narikkadan
• 63,720 points
1,599 views
0 votes
1 answer

Excel formula for searching two text in one cell and return values based on the result

You can include a second IF within ...READ MORE

answered Oct 10, 2022 in Others by narikkadan
• 63,720 points
1,157 views
0 votes
1 answer

Formula for inserting a thumbnail picture into excel cell, based on another cell's value

Here is a really excellent tutorial on ...READ MORE

answered Oct 31, 2022 in Others by narikkadan
• 63,720 points
1,443 views
0 votes
1 answer

Calculate Birthdate from an age using y,m,d in Excel

Hello, yes u can find your birthdate using ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,690 points
3,524 views
0 votes
1 answer

Calculate Birthdate from an age using y,m,d in Excel

Hi To Calculate the date, we can ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,690 points
1,873 views
0 votes
0 answers

Convert Rows to Columns with values in Excel using custom format

1 I having a Excel sheet with 1 ...READ MORE

Feb 17, 2022 in Others by Edureka
• 13,690 points
927 views
0 votes
1 answer

IF - ELSE IF - ELSE Structure in Excel

In this case, you can use nested ...READ MORE

answered Feb 18, 2022 in Others by gaurav
• 23,260 points
3,449 views
0 votes
1 answer

How to convert an excel formula to SQL to calculate daily compound interest

Here is a solution that is effective. ...READ MORE

answered Nov 26, 2022 in Others by narikkadan
• 63,720 points
812 views
0 votes
1 answer

Repeated Multiplication formula for Excel

Fun little problem using some basic maths: =A1*3^ROW(A1) READ MORE

answered Oct 8, 2022 in Others by narikkadan
• 63,720 points
949 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP