Calculation with Excel NPV, IRR, Simple and compound interest, and more

Tutor for students and financial professionals

How to calculate an income using simple and compound interest?

Simple Interest

If you have a bank account on sum $10,000 and bank pay 20% a year, you will earn $6,000 after 3 years.

Year Income Base Total
1 $2,000 $10,000 $12,000
2 $2,000 $10,000 $14,000
3 $2,000 $10,000 $16,000

FV (Future Value) = PV(Present Value) * (1 + n (number of periods) * R (interest rate per period)).

Excel has the function 'FV' which make this calculation. Our FinStat Excel templates contains the spreadsheet 'FV'. You simply enter PV, R, and n values, and the result is done.

Compound Interest

If you have the same bank account on sum $10,000 and bank pay 20% a year, you will earn $7,280 after 3 years.

Year Income Base Total
1 $2,000 $10,000 $12,000
2 $2,400 $12,000 $14,400
3 $2,880 $14,400 $17,280

FV = PV * (1 + R) ** n

Excel has the function 'FVSCHEDULE'' which make this calculation. Our FinStat Excel templates contains the spreadsheet 'SCHEDULE'. You enter PV and a serie of  R values for several periods. Then simply click a button 'Calculate'. A result is done. Please note that our product FinStat Excel templates allows to calculate FV value if even you have different interest rates in your periods. If your interest rate is the same in all periods, you can copy it. Example:

PV Rate Future value of an investment
1 20.00% 1.728
  20.00%  
  20.00%  
     

In this example the FV value is calculated for 3 periods. If you have more than 3 periods, you can simply add interest rates in the column "Rate". Then click a "Calculate" button.

Our FinStat Excel templates contains also the spreadsheet 'PV'. It allows to calculate PV value using FV, n, and R. Example:

rate n FV PV
20.00% 3 1728 -1 000.00

Future Investment Value Calculation when interest rates in periods are different with Excel

If you have $10,000 and the interest rates for years are: 1 - 20%, 2- 25%, 3- 30%.

Simple interest

Year Income per year Base Rest
1 2000 10000 12000
2 2500 10000 14500
3 3000 10000 17500

FV = PV * (1 + n1 * r1 + n2 * r2 + ... + ni * ri)

FV - Future Value

PV - Present Value

ni - duration of i peroid

ri - interest rate of i period

Compound Interest

FV = PV * ( 1 + r1) ** n1 * ( 1 + r2) ** n2  ...* ( 1 + ri) ** ni

Year Income per year Base Rest
1 2000 10000 12000
2 3000 12000 15000
3 4500 15000 19500

Income calculation with Excel when interest rates are different in periods

We have special Excel template for this purpose. Its screenshot is shown below. Alternatively, you can review our full collection of statistical and financial Excel templates.

Compound interest in Excel

To calculate a future investment value, you enter your current sum in the cell B3, values in the columns 'Number of Periods' and 'Interest Rate'. Then choose a simple or compound interest rate and click the button 'Calculate'. Now you can see the result in the cell D3.

Our collection of Excel templates contains the following spreadsheet.

1.gif (13241 bytes)

It allows to calculate compound interest sum when interest rates are different in periods. The principal in this case is current value. Both Excel templates are developed with VBA programming use.

To be continued

You also may want to review our following Excel add-ins: Excel add-ins Part 1, Excel add-ins Part 2Excel add-ins Part 3, Excel add-ins Part 4, Excel add-ins Part 5, Excel add-ins Part 6

We have also Accounting Excel Templates: Accounting Excel Templates Part 1, Accounting Excel Templates Part 2

We have Budgeting Excel templates: Budgeting Excel Templates Part 1

We have the calendars for Excel: Calendars for Excel Part 1, Calendars for Excel Part 2, Calendars for Excel Part 2

We have financial Excel templates: Financial Excel Templates Part 1

We have inventory management Excel templates: Inventory Management Excel Templates Part 1

We have planning Excel templates: Planning Excel Templates Part 1, Planning Excel Templates Part 2

We have scheduling Excel templates: Scheduling Excel Templates Part 1

We have financial Analysus Excel templates: Financial Analysis Excel Templates Part 1

 

Business Software Catalog

Business

Excel Spreadsheets

Accounting Accounting
Amortization Add Power to Excel
Archotecture & Construction Amortization
AutoCAD Analysis
Banking Architeckture & Construction
Banking with Excel Asset Register
Barcodes Auditing
Books Banking
Budgeting Budgeting
Business Calendars
Business Databases Car Accounting
Business Plans Charting
Business Resource Management Checks
Business Software with Source Code Conversion
Business Systems Download Managers

Next

Home

Excel Spreadsheets

Copyright(c) Iovsoft