Microsofts Excel Loan Amortization Schedule
This is Microsofts official basic amortization schedule template for Excel. As you can see, it has a few boxes to enter the loan information, such as loan amount and interest rate.
Then it contains an amortization table with information about each monthly payment. It also helps you see how many of your dollars are going to principal vs. interest. This template does the job, but adding some color and other design elements could make it easier to read and understand.
Two Amortization Table Tutorials In One
In this post I provide two video tutorials, with the Excel worksheets combined into one Excel workbook. The first tutorial is an in-depth Watch Me Build video on how to build a fully dynamic amortization table. The second video tutorial is short and sweet call it a bonus video, where I build a simple mortgage amortization schedule in Excel in under 90 seconds.
The in-depth Watch Me Build is a 25 minute behemoth, showing you how to build a more complex, dynamic amortization table in Excel. It includes many of the features you might find in an amortization table in an institutional model. I also share various modeling techniques that are applicable across modeling tasks.
I should note that in the video I refer to a few techniques addressed in-depth in our real estate financial modeling Accelerator program. So bear with me if a concept or two isnt fully explained. Of course, if youre watching these tutorials youll most definitely find great value in becoming an Accelerator member. So consider checking that out when you have a moment.
And with that, go ahead and download the corresponding Excel file below and do your best to follow along. Then try to re-create the models on your own.
A Few Things To Consider
There are a few important things to note here. First, we need to make two important adjustments, because were calculating monthly payments. We need to divide our annual rate by 12 to get the monthly interest rate , and we need to multiply 4 by 12 to get the total number of months in our repayment period .
Second, the is entered as a negative value because that amount is owed to the lender.
Once your boxes are filled in, hit OK.
Your spreadsheet should now look like this:
You May Like: How To Apply For A Home Mortgage
You May Like: Can You Get A Mortgage For A Modular Home
Calculation Of Periodic Payments
The primary component of the amortization tableAmortization TableThe amortization table reflects the schedule of periodic payments to be made regarding a loan undertaken, representing the principal amount and the interest amount payable in each regular installment. Thus, it is a detailed working of the loan repayment.read more for a mortgage is the periodic payment, the principal payment, and the interest payment. The periodic payment is calculated as,
Periodic Payment = Outstanding Loan * Rate of Interest * No of Periods /
The formula for interest paid during a single period is straightforward as given below,
Interest paid =Outstanding loan * Rate of interest
The principal component of the term loan in the periodic payment is calculated as,
Principal repayment =Periodic payment Interest accrued
How To Create An Amortization Schedule Using Excel Templates
Sandy Writtenhouse
With her B.S. in Information Technology, Sandy worked for many years in the IT industry as a Project Manager, Department Manager, and PMO Lead. She learned how technology can enrich both professional and personal lives by using the right tools. And, she has shared those suggestions and how-tos on many websites over time. With thousands of articles under her belt, Sandy strives to help others use technology to their advantage. Read more…
If you want an easy way to view the schedule for your loan, you can create an amortization table in Microsoft Excel. Well show you several templates that make creating this schedule easy so that you can track your loan.
You May Like: What Will Be My Mortgage
Amortization Schedule For A Variable Number Of Periods
In the above example, we built a loan amortization schedule for the predefined number of payment periods. This quick one-time solution works well for a specific loan or mortgage.
If you are looking to create a reusable amortization schedule with a variable number of periods, you will have to take a more comprehensive approach described below.
Calculating The Total Interest
Calculating the total interest paid is a simple matter of summing the values in column C. However, we will make use of our defined name, and offset that range by 3 columns to the left:
=SUM)
For this example, you should get $146,991.83. Of course, you could get the same answer with =SUM, but that wouldnt be as much fun.
Don’t Miss: Should I Get Points On My Mortgage
Using An Excel Template
Do Extra Payments Automatically Go To Principal
The interest is what you pay to borrow that money. If you make an extra payment, it may go toward any fees and interest first. … But if you designate an additional payment toward the loan as a principal-only payment, that money goes directly toward your principal assuming the lender accepts principal-only payments.
Read Also: Where To Buy Mortgage Insurance
Calculating The Remaining Balance
We can now add a column for calculating the remaining balance. In F12 enter the original balance with the formula =B2. Note that we have skipped over column E because we are going to enter the extra payment there. Now in F13, we calculate the remaining balance by subtracting the principal payment from the previous balance:
=IF> 0,F12-D13-E13,0)
Note that I am testing to see if the previous balance is greater than zero . If so, then we simply take the remaining balance and subtract the principal payment for the month and also any extra payment amount. Otherwise, we set the balance to zero. This is important so that any further calculations of the payment, principal, and interest are all zero as well .
Avoid Miscalculations With Accurate Pre
Excel amortization templates include pre-written formulas within cells to automatically calculate whatever data you need.
In a good amortization Excel template, you only need to enter a few numbers, and formulas will take over and fill out the entire table for you. No need to change each individual box if you make an error simply fix the input number, and the formulas will adjust.
Also Check: What Does It Mean Points On A Mortgage
Calculate Payments In Cell B4
To calculate the payment amount in cell B4, click “B4” and type the following equation into the formula bar at the top of the excel spreadsheet and press enter:
=ROUND, 2)
The dollar signs in this formula represent the absolute references, meaning the formula always refers to those specific cells even if copied into other locations on the spreadsheet.
Related: How To Copy a Formula in Excel in 3 Steps
How To Use Excel Formulas To Calculate A Term
Term loans can have a variety of repayment periods, interest rates, amortizing methods, and so on. Heres how to calculate amortization schedules for the two most common types of amortizing loans.
How do I calculate cumulative principal and interest for term loans? I have scoured the web for a function that will perform this task, with no avail. Lake M.
This is an interesting question because it touches on at least four issues related to the time value of money:
1. How Do You Define a Term Loan?
The definitions vary. For example, heres how different sources define a term loan:
- Business Dictionary: Asset based short-term loan payable in a fixed number of equal installments over the term of the loan. Term loans are generally provided as working capital for acquiring income producing assets that generate the cash flows for repayment of the loan.
- Wikipedia: A monetary loan that is repaid in regular payments over a set period of time. Term loans usually last between one and ten years, but may last as long as 30 years in some cases.
- Investopedia: A loan from a bank for a specific amount that has a specified repayment schedule and a fixed or floating interest rate.
2. Is the Loans Interest Rate Fixed or Floating?
Im going to assume that the interest rate is fixed. Ill cover floating rate loans at another time.
3. What Type of Loan is It? Even-Payment? Or Straight-Line?
4. What Help Does Excel Provide for Each Type of Loan?
You May Like: Can You Have A Second Mortgage With A Va Loan
What Does It Mean To Amortize A Debt
Amortization refers to the reduction of a debt over time by paying the same amount each period, usually monthly. With amortization, the payment amount consists of both principal repayment and interest on the debt. Principal is the loan balance that is still outstanding.
How to create a loan amortization schedule in Excel?
This example teaches you how to create a loan amortization schedule in Excel. 1. We use the PMT function to calculate the monthly payment on a loan with an annual interest rate of 5%, a 2-year duration and a present value of $20,000. We use named ranges for the input cells.
How to calculate effective interest amortization for bonds?
The amount of amortization is the difference between the cash paid for interest and the calculated amount of bond interest expense. Recall that when Schultz issued its bonds to yield 6%, it received $108,530. Thus, effective interest for the first six months is $108,530 X 6% X 6/12 = $3,255.90.
Mondaycoms Amortization Schedule Template
Excel can be a decent place to start when making an amortization schedule, but if you want more customization and flexibility, monday.coms template is the place to go. With drag-and-drop customization, you can quickly build and rearrange your template to match your needs.
You can also collaborate with your team in real-time and give team members varying levels of permissions so that the right people have the right amount of access. Its perfect for a distributed team that mostly works remotely.
Oh, and you can import any existing amortization schedules from Excel straight into monday.com Work OS and then export them again if needed. You can even export the template itself to a fully-functioning Excel spreadsheet.
Also Check: How Much Interest Am I Paying On My Mortgage
How To Calculate A Loan Amortization Schedule If You Know Your Monthly Payment
It’s relatively easy to produce a loan amortization schedule if you know what the monthly payment on the loan is. Starting in month one, take the total amount of the loan and multiply it by the interest rate on the loan. Then for a loan with monthly repayments, divide the result by 12 to get your monthly interest. Subtract the interest from the total monthly payment, and the remaining amount is what goes toward principal. For month two, do the same thing, except start with the remaining principal balance from month one rather than the original amount of the loan. By the end of the set loan term, your principal should be at zero.
Take a simple example: Say you have a 30-year mortgage for $240,000 at a 5% interest rate that carries a monthly payment of $1,288. In month one, you’d take $240,000 and multiply it by 5% to get $12,000. Divide that by 12, and you’d have $1,000 in interest for your first monthly payment. The remaining $288 goes toward paying down principal.
For month two, your outstanding principal balance is $240,000 minus $288, or $239,712. Multiply that by 5% and divide by 12, and you get a slightly smaller amount — $998.80 — going toward interest. Gradually over the ensuing months, less money will go toward interest, and your principal balance will get whittled down faster and faster. By month 360, you owe just $5 in interest, and the remaining $1,283 pays off the balance in full.
Simple Loan Calculator And Amortization Table
For most any type of loan, Microsoft offers a handy amortization table template for Excel. It also includes a loan repayment calculator if youre thinking about paying off or refinancing your loan.
RELATED:How to Use Goal Seek in Microsoft Excel
You can , use it in Excel for the web, or open it from the templates section of Excel on your desktop.
For the latter, open Excel, go to the Home section, and select More Templates. Type Amortization in the search box and youll see the Simple Loan Calculator. Select the template and click Create to use it.
Youll see a tool tip in the top left corner of the sheet as well as when you select the cells containing the loan details at the top. The schedule has sample data that you can simply replace.
Enter the values in the designated spot at the top of the sheet including loan amount, annual interest rate, loan period in years, and start date of the loan. The shaded amounts in the area beneath are calculated automatically.
Once you enter the above details, youll see the payment, interest, and cost section update along with the schedule at the bottom. You can then easily see how much of your payment goes toward the principal and interest with the ending balance after you make each payment.
To manage additional loans or use the calculator to estimate another loan, you can copy the sheet to a new tab.
Don’t Miss: Do Mortgage Lenders Make Commission
Understanding The Npv Function
The NPV function simply calculates the present value of a series of future cash flows. This is not rocket science.
1. For example, project A requires an initial investment of $100 . We expect a profit of $0 at the end of the first period, a profit of $0 at the end of the second period and a profit of $152.09 at the end of the third period.
Explanation: a net present value of 0 indicates that the project generates a rate of return equal to the discount rate. In other words, both options, investing your money in project A or putting your money in a high-yield savings account at an interest rate of 15%, yield an equal return.
2. We can check this. Assume you put $100 into a bank. How much will your investment be worth after 3 years at an annual interest rate of 15%? The answer is $152.09.
Note: the internal rate of return of project A equals 15%. The internal rate of return is the discount rate that makes the net present value equal to zero. Visit our page about the IRR function to learn more about this topic.
3. The NPV function simply calculates the present value of a series of future cash flows.
4. We can check this. First, we calculate the present value of each cash flow. Next, we sum these values.
Explanation: $152.09 in 3 years is worth $100 right now. $50 in 2 years is worth 37.81 right now. $25 in 1 year is worth $21.74 right now. Would you trade $159.55 for $100 right now? Of course, so project B is a good investment.
Schedule Of Loan Amortization In Excel
Let us take a home loan example for preparing a schedule of Loan Amortization in Excel. Let us assume that a home loan is issued at the beginning of month 1. The principal is $1,500,000, the interest rate is 1% per month, and the term is 60 months.
Repayments are to be made at the end of each month. The loan must be fully repaid by the end of the term.
Follow the steps to calculate loan amortization schedule.
The first step is to input the data in a standard format.
We use the PMT function given in Excel to easily calculate the monthly installments here.Here, rate = interest rate nper = period PV = is the loan amount of $1.5 million FV = is the future value of this loan amount type = 0 or 1 0 is payment done at the end of the period , and 1 is for payments at the beginning of the period . Here we assume that the payment is made at the end of the month.Note the negative sign in front of the PMT formula. It is because the repayments are cash outflows for us.
Recommended Reading: What Is A Mortgage Inspection
