### Create an Amortization Calculator in Excel

Amortization schedules are essential tools for anyone managing a loan or mortgage, providing a clear breakdown of how payments are allocated towards interest and principal over the loan’s term. Typically, these schedules detail each payment’s date, the interest portion, the principal portion, and the remaining balance, offering borrowers insight into the precise cost of borrowing over time. Creating an amortization schedule in Excel can be a time-consuming process, but with the help of some advanced payment functions, you can expedite the process and even eliminate the need for creating an entire amortization table.

## Generating the key components of an amortization schedule

Whether you’re creating a full-blown amortization schedule or just want to calculate the balance at a future point in time, you’ll need to know the following values before you can begin:

• the loan value,
• the interest rate and compounding,
• the number of periods, and
• the start date of the loan

Suppose you have a \$500,000 loan with a 5% interest rate, which is for a period of 10 years, and that payments are made on a monthly basis. Given this information, you can start by calculating the monthly payment amount. Here’s what the inputs would look like for the PMT function:

=PMT(0.05/12,10*12,-500000,0) = 5,303.28

This assumes compounding to be monthly, hence the need to divide the interest rate by 12. And the negative 500,000 balance tells the function that this is an amount owing and that it will reduce over time. The following argument, 0, is to signify that the future value is 0.

There is also an additional argument, for whether the payments are made at the beginning or the end of the period. The default is set to the end of the period. If payments are made at the beginning, then the final argument is set to 1. If the payment is at the start, then the payment value would change to \$5,281.27, assuming all the other values remain the same. But for the purposes of these examples, we’ll assume payments are made at the end of the period.

## Creating a calculator for specific month’s calculations

Now that you have all the necessary variables, including the payment amount, you can start to create the calculator.

In this first case, we’ll look at how to calculate values for just a specific period. Let’s assume the loan’s start date is January 1, 2024. And let’s assume we want to calculate what the balance, interest, and principal payment amount will be for the month of Dec 2025.

### How to calculate amortization amounts for a specific period:

1. Start with calculating the period number. One function that can help with this is the DATEDIF function. By using it, you can quickly calculate the difference between two time periods. Here’s how it would work in this example:

=DATEDIF(“1/1/2024″,”12/1/2025″,”m”) = 23

Two full years have not fully elapsed until we get to 1/1/2026. But since we want to calculate the values for the 24th month, we’ll need to add 1 to the equation. The formula to calculate the current period will be as follows:

=DATEDIF(“1/1/2024″,”12/1/2025″,”m”)+1

2. Calculate the interest payment for the period. To get the interest amount, use the IPMT function:

=IPMT(0.05/12,24,120,-500000) = 1,760.19

3. Calculate the principal amount paid down during the period. In this case, we can use the PPMT function. It is the same setup as the previous formula:

=PPMT(0.05/12,24,120,-500000) = 3,543.08

4. Calculate the ending value as of that period. Using the FV function, here’s the formula to get the ending balance as of the end of Dec 2025:

=FV(0.05/12,24,5303.28,-500000) = 418,902.68

By using these functions, you no longer need to make an entire amortization schedule. You can simply do a calculation for the period you need to sum up the values for. By creating inputs for the start of the loan, loan amount, interest rate, # of periods, and the period you want to calculate for, you can setup an amortization calculator as follows in Excel:

## Calculate amortization values for a specific range

Suppose you wanted to calculate the interest and payment values for all the months in 2025. This can be a bit trickier since we aren’t calculating amounts for just a single period anymore. Instead, we need to get a range of values.

### How to calculate amortization amounts for a specific multiple periods:

Step 1. Get the correct period numbers. Let’s assume the loan began on June 1, 2023. We need to get the starting period number, which is Jan. 1, 2025. This can be done with the DATEDIF function:

=DATEDIF(“6/1/2023″,”1/1/2025″,”m”)+1 = 20

This tells us that January is the 20th month of the loan. If January is the 20th month, then that also means if we add 11, that December will be the 31st month of the loan. Thus, our range of periods is 20 through 31. You could, however, use the DATEDIF function to do the calculation for that period as well.

Step 2. Calculate the interest payments for the year. Now that we know the periods we need to calculate the interest for, we can use the CUMIPMT function:

=-CUMIPMT(0.05/12,120,500000,20,31,0) = 20,851.88

The present value cannot be set to negative for the cumulative function, or else you will get an error. To adjust for this and avoid a negative value, simply add a dash before the function to ensure the result is flipped from a negative value to a positive one.

Step 3: Calculate the total of the principal payments made during the period. To calculate the principal paid during the period, we can use the CUMPRINC function. The inputs are the same as that of the cumulative interest payment calculation:

=-CUMPRINC(0.05/12,120,500000,20,31,0) = 42,787.43

Step 4. Get the ending period’s balance. Just as with the previous section, we can use the same FV calculation to get the ending balance. This time, it will be for period 31.

=FV(0.05/12,31,5303.28,-500000) = 393,684.23

Here is how the calculator could be setup to make these calculations based on a range.

You can download the amortization calculator I have created from these examples here.

If you liked this post on How to Create an Amortization Calculator in Excel, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.

### Free Credit Card Payment Calculator Template in Excel

Are you wondering how much you should put down on your credit card each month to ensure you aren’t taking decades to pay off your balance? With this free credit card payment calculator template, you can plug in your balance, interest rate, and your expected monthly payment (either fixed or as a percentage of the balance) to determine just how long it may take you to pay off your debt.

## How the template works

The cells that require data entry are highlighted in yellow on the template. At a minimum, the balance, interest rate, starting date, and one of the two fields for the monthly payment need to be entered in:

For the monthly payment field, you can enter in both a fixed dollar amount and a percentage of the balance. How it works is that if you enter in both values, the higher payment calculation will be used.

For example, if you had a \$10,000 balance and planned to pay 5% of it, the monthly payment would be \$500 and decline along with the balance. If you also set a fixed payment of \$400, then that would ensure your monthly payment would never fall below \$400, even as the balance declines. In effect, the payment will be the greater of these two values. If you only enter a percentage of the balance, then only a percentage will be used. Similarly, if you only have a value for the fixed amount, then that’s what your payment will remain at.

Once you’ve entered in these fields, the rest of the template will populate. This includes calculations on how many years it will take for you to pay off your balance, the end date, how much your payments will total, and the amount of interest you will have paid:

If you enter a payment that is so small it won’t even cover your interest, then the payment amount will be highlighted in red:

In the above example, the years and end date will not be accurate since, under this example, the balance will grow rather than decline over time, and thus, will never be paid off.

If you liked this free Free Credit Card Payment Calculator Template, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.

### Average Down Calculator Template

If a stock you invested in dropped in price, it could be a good opportunity to buy more shares and bring your average down. You can use the average down calculator on this page to do a quick what-if calculation to determine how many more shares you would need to be. However, you can also use this template, which will allow you to run through the same scenarios within Excel.

## How the average down template works

There are only six inputs on this template:

• Amount invested
• This is how much money you have already invested into the stock.
• Shares owned
• The number of shares that you own.
• Current share price
• What the share price is.
• Desired average price
• What price you want to average down to.
• Budget
• How much money you can afford to invest.
• Increment price by
• This is for the sensitivity analysis and determines by how much you want it to move by. The default is set to \$0.50.

Once you’ve entered that data, the rest of the template will populate. Here are the two scenarios that it will show you:

## 1. Getting to your desired average price

In this scenario, the template will show you how much to invest at different price points to get your average down to your desired average price. You will see up to 20 different data points to show you if the price continues to get lower, how many shares you will need to buy to reach the average price you are targeting.

And any scenarios that fall within your budget will be highlighted in green, and so will the corresponding chart:

If all the data points aren’t filled in or it looks like the chart doesn’t go all the way to the right, this is a sign you need to fix your Increment Price by value. Enter a smaller price increment and you’ll see more data points and a more complete chart.

## 2. How low you can get your average

The second scenario ignores the desired average price and simply tells you the different average prices you can average down to if you buy at the current price. This is good if you don’t have a specific average in mind and just want to see how low you might be able to go.

You’ll notice on the x-axis it refers to the average price rather than the share price in the earlier chart.

Please note that the template is locked down and this is to prevent overwriting formulas which could lead to errors in the calculations and the charts.

You can download the file for free, from here. The free version is limited to five price points. On the full version, there are 20 different prices, no ads, and the file is unlocked.

If you liked this Average Down Calculator Template, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.

### Price to Earnings Ratio Calculator

To enter data in the price to earnings ratio calculator, start from top to bottom. Tabbing over or hitting enter will update the calculations.

Current Stock Information

Price \$

EPS   \$

P/E

What-if Analysis

Change

to

## What P/E is and why it’s important for investors

The price-to-earnings (P/E) ratio is a key metric that many investors use when analyzing whether a stock is well-priced and a good buy, given its level of earnings. The calculation takes the current stock price and divides it by the company’s earnings per share, typically over the last four quarters. You can also calculate a forward P/E. This is what the ratio will be in the future, based on estimates of earnings.

This is a particularly useful calculation in a year like 2020 when the coronavirus pandemic has thrown many businesses out of whack and some are over or underperforming. And that means their P/E ratios may not be all that reliable right now.

Using a P/E ratio is particularly useful when comparing one stock against another. If a stock is trading at a very high P/E of 50 or more, it could be a sign that it’s overvalued. However, this can be skewed if a company is coming off a bad quarter where its profits were low. It’s always important to consider the context. And comparing different types of industries may not be helpful, either. A bank stock that is relatively stable and that may not achieve much growth will trade at a much lower P/E than a high-growth tech stock where its sales are climbing by 50% or more.

## How to use this calculator

I wanted to create a calculator that could be useful for setting up alerts. For instance, if a stock is trading at a P/E of 50 and you want to set up an alert for when it falls to a lower multiple. You can use the What-if analysis section to plug in the P/E that you want to buy it at. It will then tell you the price it will have to fall to or the EPS that it will need to rise to.

You could also use it as a simple P/E calculator. While many financial websites may give you a P/E number they won’t always update quickly, like when a company reports its earnings. If you know what the new P/E is, you can plug it into the calculator. You can also do a what-if analysis to see what the ratio will be if earnings rises or falls to a certain number.

To enter data into this calculator, you’ll want to start from the top and work your way down. Enter the price and EPS first and then make your selections in the what-if analysis. If you go straight to the what-if analysis then the calculation won’t be correct. As you’re entering data and tabbing over, the formulas will automatically update. Hitting enter after entering in a number will also update the calculation.

Another calculator you may want to try is the average down calculator, which can help you determine how many more shares you’ll need to buy to get your average price down to a specified amount.

If you liked this post on the price to earnings ratio calculator, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.

\$

per

## How this calculator works

The money spent calculator can help you determine how much a daily, weekly, or monthly recurring expense costs over the course of a year. Simply enter the dollar amount using the slider above and select how frequently you make that purchase. Then, you’ll be given a spending summary by month and by year.

And just for fun, it’ll tell you what that annual spend could’ve translated to if you bought milk, coffee, or toilet paper instead. These numbers are just estimates and will obviously vary by location but they can help illustrate the size of the expense using common items.

If you liked this money spent calculator, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.

50

per

## How this calculator works

The Time Spent Calculator is a way to see how much time a daily, weekly, or monthly activity uses up over a long period of time.

To begin, start by using the slider to determine the number of hours or minutes the specific activity takes. Then, select Minutes or Hours from the first drop-down selection. And then below the slider, use the other drop-down option to say how often the activity happens — every day, every week, or every month.

Whenever you update one of the drop-down boxes or move the slider, the calculator will update and tell you the amount of time that you’ll spend doing that activity over the course of the month, year, decade, and 50 years.

### Average Down Calculator

Scroll further down if you would like to see details as to how this calculator works and a description of it.

There is a new version of this calculator available here (mobile-friendly) that will make calculations at multiple price points at once (use the new interval field to specify how much in price you want to jump by). It will also let you know how low you can average down at the current share price.

Also, be sure to check out this free average down calculator template in Excel.

#### Average Down Calculator

Amount Invested
Shares Owned Today
Current Average Price
Current Share Price
Desired Average Price

## The purpose of this calculator

If you invest in stocks and want to know how much it would cost you to average down, this calculator will help you do just that. Averaging down is a great way to take advantage of a stock that’s dipped in value and that you’re confident won’t stay there. By purchasing more shares of a stock at a lower price, you’re bringing down the average cost of your total investment. And that means you’ll need the stock to rise to a lower price than before to turn a profit. Or if you’re already in the black, then you can put yourself in a great position to increase those profits.

## How the average down calculator works

To use this calculator, you’ll need to enter the total dollars that you’ve invested in a stock, how many shares of it you own, what the current price of the stock is today (or the price that you plan to buy it at), as well as what price you want to average down to. Then, click on the Calculate button. The calculator will then tell you how many shares you’ll need to buy and how much it will cost you in order for you to get to that average.

Note that since you can’t average down below what the current share price is, you’ll have to make sure that your desired average price is higher than where the stock is today. Here is the calculator:

If you liked this free average down calculator, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download.

### Compounded Annual Growth Rate Calculator

If you don’t want to calculate compounded annual growth rate yourself, you can do it quickly and easily with this free calculator:

Calculating Compounded Annual Growth (CAGR)

 Percent Change Number of Years

`[WP-Coder id="1"]`