How to find interest & Principal payments on a loan in Excel
This article lists some of the built-in Excel formulas that can be used for depreciation calculations You can download the Excel templates for examples of how formulas can be used Most of the examples are applicable to loans because the amortization refers generally to pay a loan in regular installments payments at the beginning, you are mostly paying interest, but principal amount owed decreases even more of the payment goes to repay principal rather than interest.
The Excel Help file does a good job of explaining the following functions, but examples of spreadsheets demonstrate how we might use some of these formulas.
ISPMT rate, by NPM, pv - The amount of interest paid during a given period.
rate VPM NPM, pv - The periodic payment amount.
NPM rate, PMT, PV - The number of payments.
CUMIPMT rates, NPM, pv, n1, n2, 0 - payment of cumulative interest for the periods n1 n2.
CUMPRINC rates, NPM, pv, n1, n2, 0 - cumulative principal payment for the periods n1 n2.
nominal_rate EFFECT compounding_periods_per_year - Calculates the annual effective interest rate See Excel Help file on this feature.
These formulas require you to install the Scan utility, which comes with Excel, but is often installed automatically To install the, open Excel and go to the Tools Add-Ins menu and check the next utility box analysis.
For n1 n2 - specific period between 1 and NPM.
Pv - the present value of the loan ith the loan amount.
In Excel I generally do not like to use financial formulas embedded unless I do not understand how they work for depreciation formulas, I think the best way to understand the equations is to create an amortization table loan or table to see what actually happens in a payment period to another.
For starters, the following Excel spreadsheet creates an array of very basic graphics and amortization In this worksheet, the only special Excel formula used is the PMT function to determine the monthly payment.
One thing you need to do with the spreadsheet above is to look at what happens when you change the length of the loan Pay particular attention to the graph which compares the cumulative interest paid for capital report.
The following worksheet was made specifically to provide an example of using VPM formulas NPM CUMIPMT and CUMPRINC It includes two depreciation tables The first shows the monthly payments and the second shows the cumulative sum of interest and principal from year to year payments are made monthly, but CUMIPMT CUMPRINC and functions can be used to calculate the cumulative totals if the interest rate is fixed and payments are constant assuming no additional payments are performed.
This worksheet is an amortization calculator fixed rate loan that creates a payment schedule for the monthly payments on a simple mortgage or another loan with a duration of 1 to 30 years.
No installation, no macros - Just a simple spreadsheet.
Depreciation formulas in Excel, depreciation, formulas, Excel, Senior cumulative interest.