Excel Tip: The PMT function - calculating loan payments in Excel

Excel has a baffling array of functions under the heading of "Financial", many of which are only likely to be used by financial analysts. However, over the next few weeks, I will tell you about a few that can be very useful to the rest of us.

This week, I want to introduce you to the PMT function, which allows you to calculate payments on a loan.

Before we get into how the PMT function works, it is useful to note what types of loan it will work for (and what types it won't!).

The function works for loans with a constant interest rate and regular payments of the same amount. The only exception to this is that it does allow for a 'balloon payment' at the end of the loan.

If this is the nature of the loan payment you want to calculate, then the PMT function will work for you.

Essentially, you feed the function the loan details, and it returns the regular payment amount.

The syntax of the PMT function is:

=PMT(Interest Rate,No of Periods,Present Value,[Future Value],[Type])

The last two arguments are optional and we'll come to those later in this post.

The three mandatory arguments are explained below:

Interest Rate: This is the interest rate per payment period, so if you have an annual interest rate of 6% with monthly payments, the Interest Rate entered shound be 0.5% (i.e. 6% divided by 12).

No of Periods: This is the number of periods or payments of the loan. A 5 year loan with monthly payments would be 60, for example.

Present Value: This is the value, today, of the loan and for a new loan will be the loan advance amount.

So, say we have a £10,000 loan over 5 years with monthly payments and an annual interest rate of 12%:

Interest Rate = 12%/12 = 1% or .01
No of Periods = 5 x 12 = 60
Present Value = £10,000

So our function becomes:

=PMT(.01,60,10000)

and returns a monthly payment of £222.44. Note that the function actually returns -222.44 as we entered Present Value as  positive figure. If the receipt of £10,000 is positive, then for consistency, the payment returned is negative. We could enter the £10,000 as a negative number (i.e. a negative payment), which would return a positive payment.

In reality, we wouldn't normally enter these figures into the function directly, more likely we would hold them in cells so that we could experiment with changing their values. and the function would be more like =PMT(B1,B2,B3), where Interest Rate, No of Periods and Present Value were held in cells B1, B2 and B3 respectively.

The final two (optional) arguments work as follows:

[Future Value]: This is where you would enter a 'balloon' payment, or any balance that you wish to remain outstanding at the end of the period you are looking at. You might have a final 'balloon' payment of £5,000, and therefore the payment calculated would need to leave exactly £5,000 outstanding when No of Periods comes to an end. Alternatively, you might recalculate payments annually to hit a specific target balance at the end of each year. This should be entered with the same sign as the payments (i.e. the opposite sign to Present Value). This arguments is treated as zero if omitted, i.e. the loan will be completely paid off after all payments are made.

[Type]: This is a 1 or a 0 and indicates when in each period the payments are made. If this is 0, or omitted, the calculation assumes that payments are made at the end of each of the periods in No of Periods. Enter 1 for this argument if payments are made at the start of each period.


If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies "The 5 Excel features that you NEED to know" and "30 Chants for Better Charts".

Excel Tip: Using formulae in text boxes and chart titles

Do you have charts with titles that regularly need to be changed? Or text boxes in diagrams that need to be edited to reflect the numbers shown?

It is really easy to have these update automatically, but it is not obvious how you are supposed to do it in Excel.

Say you have several charts that feature in a monthly pack and each chart refers to the current month in its title. "Cumulative Profit to November 2014" for example.

In a normal cell we can create this sentence in a formula. If A1 contains the period-end date, then we can use the following:

="Cumulative Profit to "&TEXT(A1,"mmmm yyyy")

Unfortunately, we cannot just type a formula like that into a chart title or a text box,  but we can do the next best thing.

We could type our function above into, say, cell B1, then go to edit the chart title or text box (so we can see the cursor ready for us to type). We can then click in the formula bar and type =B1. The chart title or text box will then always show the contents of cell B1.

No more editing charts each month!

We can use exactly the same technique in a text box, which might refer to this month's sales figure (held in cell A2). Our function would be something like:

="Your sales this month are "&TEXT(A2,"£#,0")

Once the text in our charts and diagrams is automated like this, they can simply be an output from the numbers, requiring no manual input.

Addendum: There is a little oddity when it is a chart title (not a text box). If the reference is on the same sheet, it must still have it's full name, e.g. =Sheet1!A1 rather than =A1. This is not an issue when the data is on a separate sheet.




If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies "The 5 Excel features that you NEED to know" and "30 Chants for Better Charts".

Excel Tip: The Camera Tool - using parts of your spreadsheet in graphics or charts

This week, I just have a short post about a simple tool that is a bit of a hidden gem in Excel.

We know we can add graphics to an Excel spreadsheet by using the Insert ribbon to add images and shapes, as well as the many charts that we can create in Excel.

But what about when, instead of adding a bit of graphics to a spreadsheet, we want to add a bit of a spreadsheet to some graphics?

This might be having a table appear within the area of a chart, or in front of an image, for example.

We can do this easily using the Camera tool.

Before we can do anything, we need to make the camera tool accessible. Microsoft don't make it easy to find!

To do this we need to add it to a ribbon. We can do this as follows:

• Select File, Options and Customize Ribbon
• From the "Choose commands from:" drop-down, select "All commands"
• Scroll down to find "Camera" (they're alphabetical)
• Highlight any custom tab on the right hand side (the simplest is Home, Custom Edit)
• Click "Add>>"
• Click "OK"

You will now see this at the right hand end of the home ribbon.

Now we have the tool, it is really easy to use.

Say we have a small table in cells B3:D5 that we want to use within a chart, in front of an image, or within a set of shapes.

All we have to do is select cells B3:D5 and click the Camera Tool. The cursor turns into the same cross hairs you see when creating a shape, but this time you can draw the shape within your graphics (which don't have to be on the same sheet as the original table), and the result becomes an object (just like the shapes) that you can manipulate in the same way.

It's that simple!

If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies "The 5 Excel features that you NEED to know" and "30 Chants for Better Charts".

Excel Tip: Copying and pasting a row automatically

Regular readers of my blog will know that I am not normally a big fan of Macros. I would argue that in the majority of situations that I see them used, they are unnecessary and often result from a lack of understanding of how Excel can address the problem using its standard functionality.

Naturally this is not always the case and one particular situation that cannot easily be addressed using standard functionality is automatically copying a line of data from one row to another.

Before we get into it, I think it is important to point out that there are many times that I speak to a client who thinks this is what they need, when what they really need is a link between the two lists, or one list with a category field that can be used to report two lists from the same data.

I came across an example of when this was genuinely need just last week. In automating an accountancy practice's job tracking, I had brought all of their data together into three distinct lists - Clients, Recurring Jobs and actual Jobs.

This allowed them to store information at the right level:

Clients - One row per client (e.g. XYZ Ltd), holding all information pertaining to that client.

Recurring Jobs - One row per each recurring job (e.g. XYZ Ltd Annual Accounts), holding the current information relating to that particular recurring job.

Actual Jobs - One row for each actual job, which could be a one-off job or an instance of a recurring job (e.g. XYZ Ltd Annual Accounts 2014). This holds all information for that specific job.

Each Client, Recurring Job and Actual Job then has its own unique reference that is used to link them and access information from the other lists (using lookups and SUMIFs, etc.)

However, on the Actual Jobs list, we need a new instance each time a recurring job comes around. We also want to copy the recurring information over, with the ability to edit it just for this instance. That means copying and pasting a number of columns from Recurring Jobs to Actual Jobs each time.

This can, of course, be done manually, however that can be cumbersome and prone to error - not least in that in this particular instance I wanted to use "paste as values" rather than a full paste, as this could override conditional formatting that I had used on the Actual Jobs sheet.

However we can create a relatively simple macro to do it for us.

To add a macro you will need the Developer Ribbon which you can enable by selecting File, Options, Customize Ribbon and ticking the Developer option on the right hand side.

From the Developer Ribbon, click Macros and enter the name (e.g. CreateJob) that you want to give this Macro and click Create.

Then paste the following code between the lines already there, being Sub CreateJob() (or whatever name you gave the Macro) and End Sub:

    If ActiveSheet.Name = "Recurring Job Data" Then
    Range(Cells(ActiveCell.Row, "A"), Cells(ActiveCell.Row, "F")).Select
    Selection.Copy
    Sheets("ActualJobs").Select
    If ActiveSheet.AutoFilterMode = True Then
    ActiveSheet.AutoFilterMode = False
    End If
    Range("A10001").End(xlUp).Offset(1, 0).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Rows("1:1").Select
    Selection.AutoFilter
Else
Exit Sub
End If

The yellow items will need to be changed for your individual requirements:

Recurring Job Data is the name of the worksheet that we want to copy from. This part of the code, checks whether we are on this worksheet when the code is run, and exits the code if we are not. That stops the code running when we are on the wrong worksheet.

The A and F are the columns (from and to) that we want to copy over.

ActualJobs is the worksheet that we want to copy to.

A10001 is a cell in the first column of the worksheet that we want to copy to, beyond the bottom of the range of the existing data.

The code works as follows:

• Check the active worksheet is "Recurring Job Data" and exit the Macro if not
• Select columns A to F of the current row (the row that that the currently selected cell is in)
• Copy these cells
• Switch to the "ActualJobs" worksheet
• Switch off Autofilter (as if the list is filtered this may cause the code to incorrectly determine the bottom of the existing list
• Select cell A10001
• Go up column A until it finds some data and selects the cell below this
• Paste as values
• Switch Autofilter back on (assuming that the headings are in row 1)

Note that the code requires all rows that have data to have row A populated.

Once we have entered the code, we can select File, Close and return to Microsoft Excel.

Finally, we want to attach this code to a shortcut key. we can do this by clicking Macros again selecting our Macro from the list and choosing Options. Here we can enter the key that when pressed with the Ctrl key will activate the Macro. I chose J for job.

Then all we need to do to copy a recurring job to the ActualJobs sheet is click any cell in the row on the recurring job sheet and press Ctrl J.

If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies "The 5 Excel features that you NEED to know" and "30 Chants for Better Charts".