Excel for Finance: EMI, SIP and Loan Calculator

One of the most powerful tools for managing personal finance is Microsoft Excel. We can use Excel for many financial calculations. For e.g., planning a loan, calculating monthly EMIs or for calculating the maturity amount of mutual fund SIPs after 10 years. Thus, Excel finance calculator can be our go to tool for quick, accurate and auto updating calculations.

In this guide, we will calculate the following using Excel finance calculator –

1. EMI (Equated Monthly Installment)
2. SIP (Systematic Investment Plan) returns
3. Loan amortization schedules

Beginners as well as intermediate users will find this guide a perfect solution for building financial calculators without needing complex financial software.

———————————————————————————————————————————————————-

📌 1. EMI Calculator in Excel

EMI  stands for – Equated Monthly Installment. It is the amount you pay every month to pay off a loan. It has two components – principal and interest. EMI is designed in a way that you pay the same amount every month for the whole duration of the loan.

EMI Formula in Excel

Write this formula in a cell –

=PMT(rate, nper, pv)

• rate = Interest rate per period. it is monthly interest rate in this case (Annual Rate ÷ 12).
• nper = Total number of payments. For example, if the loan tenure is 20 years, there will be 20*12=240 payments.
• pv = Present value (loan amount)

For example, let’s try this case – You availed a home loan of $20,00,000/- at 8% annual interest for 20 years. Now calculate the monthly EMI you will pay to the lending institution.

Start by noting down what we know –

1. Loan Amount (PV) = $20,00,000/-
2. Annual Interest Rate = 8%
3. Loan Tenure in Years = 20

Now put these in the EMI Formula:

=PMT(8%/12, 20*12, -2000000)

Result = ₹16,728.47

> The minus sign before 2000000 is required as it denotes cash outflow.

🔄 Amortization Table (Optional)

If you wish to build an entire loan schedule table, showing the split of EMI into principal and interest component, you can do that by using functions like PPMT() and IPMT().

• =PPMT(rate, per, nper, pv, [fv], [type]) – here per is the specific period you’re interested in (like 32nd month, 50th month), fv is the future value (optional, defaults to 0), and type indicates when payments are due (0 for end, 1 for beginning). Rest values are same as before. You will get how much of the EMI went towards paying principal.
• =IPMT(rate, per, nper, pv, [fv], [type]) will give us the interest component of EMI. Or we can simply do EMI – Principal to calculate it.

———————————————————————————————————————————————————-

📌 2. SIP Calculator in Excel

Systematic Investment Plan (SIP) is presently a popular method of investing a fixed amount every month / quarter / year in mutual funds and other similar financial instruments.

Here is the formula for calculating SIP Maturity –

=FV(rate, nper, pmt, [pv], [type])

• rate = Expected rate of return per period, like 12% per annum
• nper = Total number of periods
• pmt = Amount invested per period (monthly)
• type = 1 (if payment is made at beginning of month), 0 (end of month)

For example, we decided to invest $5,000/- monthly for 10 years and expecting 12% annual return. Now we need to calculate the maturity value after 10 years –

1. Monthly Investment (PMT) = $5,000
2. Annual Return Rate = 12%
3. Duration (Years) = 10

Then, let’s put these in the formula –

=FV(12%/12, 10*12, -5000, 0, 1)

Thus, we get = $11,61,695.36/- after 10 years as maturity amount.

———————————————————————————————————————————————————-

📌 3. Loan Amortization Schedule

A loan amortization is a table which gives month by month illustration / break-up of each EMI into following components:

• Principal repayment
• Interest payment
• Remaining balance

This is particularly helpful for home loans, car loans, or personal loans.

Required Excel Functions

• PMT() – for monthly EMI
• PPMT() – principal component of EMI
• IPMT() – interest component of EMI

For example, let’s discuss a Car Loan of $5,00,000 at 9% for 5 years. Note down the known values as below –

1. Loan Amount (PV) = $5,00,000/-
2. Interest Rate
3. (Annual) = 9%
4. Tenure (Years) = 5

Then, put these values into above formulas like this –

1. EMI:

=PMT(9%/12, 5*12, -500000) = $10,379.18/-

2. Principal & Interest (for each month):

• Month 1 Interest =IPMT(9%/12, 1, 60, -500000)
• Month 1 Principal =PPMT(9%/12, 1, 60, -500000)

3. Remaining Balance = Previous Balance – Principal Paid

Then, just drag this formula for 60 rows and complete shcedule will be ready. If the remaining balance is 0 in the 60th row, the calculation is correct.

If you can not get it right, download this Excel for the ready to use solution –

Loan Amortization

———————————————————————————————————————————————————-

📝 Practice Questions

Try these on your own and let us know in comments if you get stuck anywhere –

1. EMI Practice:
Firstly, calculate the EMI for a $15,00,000 loan at 11% rate of interest per annum for 7 years.

2. SIP Practice:
Then try this one – You decided to invest $2,000 per month for 15 years at 10% annual return, find out your maturity amount.

3. Amortization Table:
Lastly and similar to the example discussed above, create a 12-month amortization table for a $1,00,000 loan at 12% for 1 year.

———————————————————————————————————————————————————-

🧠 Tips and Tricks

Furthermore, please follow these tips for making your tables and calculator user friendly for other users too –

• Do not forget to straightaway divide annual interest rate by 12 for monthly calculations.
• Secondly, use Named Ranges in Excel for cleaner formulas.
• Use currency symbols while working with amounts for readability.
• Certainly use Data Validation drop-downs to make interactive calculators.
• Lastly, save templates for reuse.

———————————————————————————————————————————————————-

🗒️ Summary

Excel certainly gives you the ability to effectively manage your own finances. With just a few calculations, you can compute everything, including loan schedules, SIPs, and EMIs with Excel finance calculator.

Hence, you don’t have to be an expert in finance. Basically, just enter the data, and Excel will take care of the rest.

Also check out our other articles on –

Date and Time Functions in Excel: A Complete Guide

How to Create Dependent Drop Down List in Excel

Excel Power Pivot for Beginners: Create Data Models Easily

Additional Calculator Templates