How to Calculate How Much Money an Investment Will Earn Over Time With Revolving Interest
A spreadsheet can calculate variable compound interest.
Jupiterimages/Photos.com/Getty Images
If a constant interest rate acts on your investment, you can calculate your returns with a simple formula. You can similarly calculate your returns if the interest rate grows continually or if the interest rate follows a mathematical function. But if the interest revolves between several values, you have to calculate each period's returns individually. A quick way to do this if the investment covers many periods is to use a spreadsheet.
Open a new spreadsheet and type the initial interest rate that acts on your investment in cell A1. For example, if you will earn 0.39 percent in the first month of your investment, type "0.39 %."
Step 2Type successive interest rates in other cells in Row A, starting with A2.
Step 3Type your investment's initial principal in cell B1.
Step 4Type "=B1*(1+A1)" in to cell B2, and then press "Enter" to calculate your investment's worth after one period.
Step 5Click the black square in the lower right corner of cell B2 and drag it downward to extend the formula into lower cells. The spreadsheet now displays your principal for each period in row B.
Step 6Type "=B2-B1" into cell C2 to calculate your returns for the investment's first period.
Step 7Click the black square in the lower right corner of cell C2 and drag it downward to extend the formula into lower cells. The spreadsheet now displays your earnings during each period in row C.
Step 8Type "=B2-$B$1" into cell D2 to calculate your cumulative earnings after the investment's first period.
Step 9Click the black square in the lower right corner of cell D2 and drag it downward to extend the formula into lower cells. The spreadsheet now displays each period's cumulative earnings in row D.
References
Resources
Writer Bio
Ryan Menezes is a professional writer and blogger. He has a Bachelor of Science in journalism from Boston University and has written for the American Civil Liberties Union, the marketing firm InSegment and the project management service Assembla. He is also a member of Mensa and the American Parliamentary Debate Association.