Paste values!

Did you know you can easily paste copied data as values?

Why?

1) To keep your original formatting
If you do a standard paste (e.g. Ctrl V), everything gets pasted including formats - this includes font size and colour, background colour, and borders! Here you can compare a standard paste with a value paste - which do you prefer?

2) To avoid lots of “clutter”
Because a standard paste copies everything including cell protection, conditional formatting, and data validation, repeated use just clogs up your spreadsheet with unnecessary content. This can increase file size and can really hinder usage if you are using sheet protection.

3) To replace formulas with results (text or values)
This can be useful if you added some data to a table using a lookup formula but just want to keep the results as text or numbers.

How? Easy!

Mouse: Instead of just using standard paste (e.g. Paste button) click the down arrow to get options and then use the “paste values” button (clipboard icon with 123).

Keyboard: Use Ctrl V, followed by Ctrl to get a drop-down list of options then V to select Values. (Thanks to Wyn Hopkins for that tip).

Or you can use the 4-key sequence Alt H V V.

Have fun with that!

Check out all the other ⚡ special paste options in our separate blog.

Create and use flexible loan amortisation schedules

Create an interest and repayment plan for a loan yourself in Excel - we explain how! Or simply download our free Loan_Amortisation_Schedules with four plan versions, ready to use.

When do I need this?

If you need to plan an annuity or constant repayment loan (amortising loan) with regular (usually monthly) payments, perhaps as part of a financial model. Such a schedule can also be used to calculate liability values for finance leases / IFRS16. It can also be used to compare various financing alternatives with different terms and different interest rates.

Or maybe you just want to get a better understanding of that mortgage payment plan your bank is sending you. 😊

What is an amortising loan?

An amortising loan must be repaid (amortised) regularly over the term. There are two variants:

1. Equal instalments (annuity)

An annuity is a loan (or finance lease liability) where the instalments (interest and principal combined) remain constant throughout the term. Such loans are popular for both lenders and borrowers because the monthly cash flows are constant and predictable.

Each loan repayment includes some interest and some principal repayment which reduces the outstanding loan. Initially, the outstanding loan balance is at its highest and therefore the interest cost is also at its highest. As payments get made, the loan balance reduces gradually and consequently the amount of interest reduces so that more and more of the fixed monthly repayment goes towards repaying the loan.

2. Equal repayments

In the case of a loan with equal repayments, the amount of the regular payment to be made (interest and repayment together) decreases over time, because the repayment instalments remain the same but the interest on the remaining capital decreases.

How do I capture the basic loan details?

Create an input section for the basic data such as loan term etc., like this.

How do I calculate the monthly amounts?

Create a calculation section like this. Having the dates and amounts along the rows is standard practice in the financial industry. However, if using the calculations as an input for a financial model which is usually built across columns, it could make sense to build the loan schedule in columns as well. Just make sure you get the cell references correct.

You need a row for each period. In each period, you must calculate the interest, the principal, and the total instalment plus the closing balance. See below for details of calculation methods and formulas. A totals row at the top gives you a great overview and is ideal to check your results including the final closing balance (use an INDEX/MATCH function to find your closing balance).

To make things easy for you, we have created a file with four loan schedules which you can download free of charge: Loan_Amortisation_Schedules.

They also include visualisations to show how the payments and balances change over time.

 How can I use one of the loan schedules available in downloads?

You can use any one of them ‘as is’ by simply amending the assumptions (grey input cells at the top). Alternatively, you can incorporate one into an existing model. In this case, copy the relevant calculation into your model, then link the interest costs to your profit and loss account (income statement) and the closing balances to your balance sheet. For a cash planning tool, link the total installments as cash outflows to your cash flow plan.

What does each schedule do, exactly?

Each schedule automatically calculates your monthly instalments (split into interest and principal) and the remaining balance. Furthermore, it visually highlights the current period row in green based on today’s date.

In schedule 4 (fixed principal repayment) you can also enter one-off repayments as inputs right next to each period. The remaining principal repayments will automatically be adjusted.

There are four versions - what are the differences?

The four versions differ in the way the interest, principal repayment and the total instalment are calculated, as follows.

Vers.

Short description

Interest

+ Principal
repayment

= Total instalment

Closing balance

1

Uses Excel financial functions

=IPMT(rate, per, nper, pv, [fv], [type])

=PPMT(rate, per,
nper, pv, [fv], [type])

=Interest + Principal

=Closing bal.
prior period
- principal
repayment

2

Mostly uses  mathematical operators

=Outstanding loan balance * annual interest rate/12

=Total instalment
- Interest

=PMT(rate, nper, pv, [fv],
[type = 0])

Same as
version 1

3

Same as version 2, but payments in advance

First period is zero, otherwise same as version 2

Same as
version 2

=PMT(rate, nper, pv, [fv],
[type = 1]) plus balloon in final month

Same as
version 1

4

Same as version 2, but with fixed principal repayments

Same as
version 2

Equal  instalments
reduced by any one-off repayments

=Interest + Principal

Same as
version 1

 Notes

  • Version 1: Excel functions do the hard work for you!
    Any balloon payment is shown as a remaining balance at the end of the term.

  • Version 2: Some modellers are more comfortable with this approach and it is more flexible than version 1 e.g., if you need to model ad-hoc repayments.

  • Versions 1 and 2: These are both based on payments in arrears and yield the same results. There are almost always various routes to success in Excel and this is a great example.

  • Version 3: Covers the special case of lease contracts with payments in advance and a residual value guaranteed by the lessee at the end of the term (common structure in Germany). The guaranteed residual value/balloon payment is shown as being paid at the end of the last month of the term. It still accrues interest over that period.

  • Version 4: Payments in arrears with a fixed principal repayment that changes only if additional one-off principal payments are made.
    Any balloon payment is shown as a payment at the end of the term.

In the Excel financial functions, what do ‘rate’, ‘per’ etc. mean? 

All three financial functions used (IPMT, PPMT and PMT) use the same arguments which are now explained. To help you, we added the relevant term next to each of the input cells.

  • nper: The total number of payment periods for your annuity.

  • per: The period number for which you want to find the interest or principal repayment. Must be in the range 1 to nper. (This figure is not in the assumptions but in the first column in the loan schedule, called ‘Period’).

  • rate: The interest rate per period, usually the monthly or annual rate.

  • pv: The present value (initial amount) of the loan or financial lease.

  • fv (optional): The future value or balance after the last payment is made. If fv is omitted, it is assumed to be 0. The future value of a loan, for example, is typically 0.

  • type (optional): Enter 0 for payments made at end of each period or 1 for payments made at the beginning of each period. If type is omitted, it is assumed to be 0.

Warning! Take care when using these functions that the rate, per and nper arguments match up i.e., if you are using monthly periods (as here), then the interest rate also has to be monthly not annual. If you mix them up you will get incorrect results!

Also, make sure you use the correct signs in the formula i.e., when pv is positive, fv needs to be negative and the other way around to get the correct values. If you use pv as a positive number, your formula results will be negative numbers (which you might have to convert depending on what you are planning to use them for).

Which version is best to use?

For standard loan structures (payment in arrears) you are free to choose your preferred version between versions 1 and 2. For leasing structures with payment in advance version 3 is your template of choice. For loans with equal principal repayments in each period (with or without additional / one-off  principal repayments), please use version 4.

What special features do the schedules have?

  • Data validation for the terms input cell (D6): inputs must be a whole number between one and 360 in order for the formulas to work correctly.

How? Please select cell D6, use the menu Data , Data Validation, and review the ‘Settings’ and ‘Error Alert’ tabs

  • Conditional formatting for the table: the data for the current month is automatically highlighted with a green background.

How? Please select any relevant cell, use the menu Home, Conditional Formatting, Manage Rules and review the settings

 

 

Improve your Excel and climb Maslow’s pyramid

What do the motivation levels of the Excel pyramid mean for you and how can you climb them? In this blog I will answer both questions and give you links to useful resources.

In time-honoured tradition, we start at the bottom and work our way up through the levels. Before we start, there is one piece of valuable advice which is valid for all levels:

Constantly learn and grow your Excel skills and practice these as often as you can to make sure they become second nature. Or as I like to say at the end of my training courses: “use it or lose it!"

 

Physiological

You have a PC or laptop with MS Office installed, can open Excel and enter data and formulas

This level is easy to achieve. If you have a PC or laptop, you probably have MS Office already installed. Fire up Excel and you are ready to get cracking with data and calculations. Especially, but not only, for beginners I recommend the following great website.

https://www.excel-easy.com/

This has hundreds of practical examples organised in useful topics. Pick a topic area and work through the pages and examples to learn the topic. Alternatively, you can search the entire site using the search box at the upper right of the start page to find what you are looking for.

 

Safety

You develop your spreadsheets according to best-practice (e.g., clear inputs, no hard-coding) and include model checks to catch errors

Once you have the basics under your belt, it is important to learn best practice so that you do not develop bad habits such as hard-coding (entering a number in a formula rather than referring to an input cell with the value) and to help ensure that your spreadsheets are reliable.

For this you can follow

Gary's Golden Ground Rules

or the „20 Principles for Good Spreadsheet Practice“ from the Institute of Chartered Accountants of England and Wales (ICAEW).

https://www.icaew.com/technical/technology/excel-community/twenty-principles

 

Love/belonging

Users love using your spreadsheets because they have user-friendly design and great functionality

Cash may be king for financial models, but outputs are king for spreadsheets. What you need is clarity and impact. Where better to find out more than a website and book called “Clarity and impact” from Jon Moon.

https://www.jmoon.co.uk/

Among other things, you will learn the four principles of great design: contrast, repetition, alignment, and proximity which can also be applied to spreadsheets (and which just happen to have an easy-to-remember acronym!)

For great functionality, I suggest you learn tricks and tips. One useful website is the one you are currently on! To my knowledge, we are unique in offering all content in both English and German.

www.how2excel.com

I am a fan of the ExcelJet website, where I learned dynamic arrays. The site includes short training videos and you can also sign up for a useful newsletter.

https://exceljet.net/

If you like youtube, check out Leila Gharani’s channel which offers lots of great learning videos for Excel.

https://www.youtube.com/channel/UCJtUOos_MwJa_Ewii-R3cJA

 

Esteem

You are valued because your spreadsheets help users in their work

At the latest by this stage, you need to adopt a formal approach to developing your workbooks. The best way to learn here is with structured training courses which take you through a topic step by step, building up your knowledge and your skills as you go.

I am a big fan of Maven Analytics – their online courses are very professional and well-structured and often have useful additional material to download. These includes courses on Power Query and Power BI. You can buy their courses on Udemy. Never pay full price here - all courses are regularly on offer for around €14.99.

https://www.udemy.com/courses/search/?q=maven+analytics&src=sac&kw=maven+anal

If live training is more suitable for you or your organisation, my consulting business Knott Consulting offers that – either on-site or remote, in English or German. We offer a broad range of courses from data analysis and planning to intermediate and advanced business modelling, as well as customised courses e.g., for Power Query and VBA macros. Participants complete structured case studies with support from experienced and motivated trainers to maximise their learn effect. For more details, please visit our website

https://www.knott-consulting.com/en/excel-training/

 

Self-actualisation

You are an Excel wizard and know how to excel at any calculation

On your way to reaching this level you will need to learn to develop 3-way financial models, so called because they include three financial statements: profit and loss account (sometimes called income statement), balance sheet and cashflow, which all impact one another. A good start is the free course from Gridlines.

https://academy.gridlines.com/p/essential-financial-modelling

At Knott Consulting (see link above) we offer this as well as M&A modelling and discounted cash flows (DCF) as live training (on-site or remote). Alternatively, you can teach yourself M&A and DCF modelling e.g. at Macabacus.

https://macabacus.com/learn

You may choose to get professionally certified by either CFI or FMI:

https://corporatefinanceinstitute.com/collections/ (FMVA® certification)

https://fminstitute.com/ (3 levels: Advanced, Chartered and Master Financial Modeler)

The CFI certification includes course material. For FMI, you need to use an external trainer such as Danielle Stein Fairhurst. Her company Plum Solutions offers FMI exam preparation and a range of other training courses.

https://plumsolutions.com.au/

And finally, you can sign up to be a Full Stack Modeller. This covers a wide range of skills needed by Excel masters from soft skills like having the correct mindset through to the latest tools technology has to offer in modelling, data processing and analysis.

https://www.fullstackmodeller.com/

 

The path to the top

Those are not the only routes you can take. There are lots of other great sites and trainers out there, I have mentioned here those sites and people that have particularly inspired me or others I know or who I have learnt a lot from. Whatever path you take, get out there and learn and practice to climb the Excel pyramid.

I would like to close this blog by quoting words of wisdom from the great (Jedi) master himself, Yoda!

“Always pass on what you have learned.”

That way, others can benefit from your knowledge and skills – and that is the best reward of all! 😊

Learn 3 quick tips for filters

Filters are great for, well, filtering data so you only see rows of data which you are interested in, e.g., invoices for a certain customer. Here I show you three quick tips to help you in your daily work.

Tip 1: Use a shortcut to turn filters on

Select any cell in your header row or in the data and use the shortcut Ctrl Shift L to turn on the filters in the first row. If you add an extra column at the right of your data, this is not automatically added to the filter range. Use the shortcut twice to turn the old filters off and the new filters on to include the new column.

Tip 2: Avoid empty or incomplete filter lists

If a filter shows no data, or is missing items, this is because Excel did not correctly recognise the data range e.g., because you have  a blank row at the top of your data. In this case, you should manually select the complete data range that you wish to filter, then turn the filter on.

Tip 3: Quickly filter for #N/A values

If you want to filter for #N/A entries to investigate errors, the usual method is to deselect all items, then scroll down to the list and select the entry #N/A which is always at the end of the list. If the number of items is long, this can take a while.

A much quicker approach is to simply type # in the search box... and that's it!

A similar trick can be used to filter for blanks which are also shown at the end of the list. Here you can start to type the word (Blanks) into the search field - usually the opening bracket ( is sufficient to filter the list.

Your vote counts! It would be much easier if Excel would show the #N/As and (blanks) at the top of the filter list. Vote for this idea here.

Want more filter tips? Watch my short filter tips video.

Simulate Christmas tree lights in Excel

This is the final result...

I will show you how to develop this in just 4 steps.

Step 1 - Paint your tree

Make columns narrow, add background colours then add a star using the character map and the font Wingdings.

Paste into the cell above the tree, it looks like this «.

Then change the font, size and colour of the "star cell" so it looks like a star.

Step 2 - Add formulas

Enter this formula in all tree cells:

=RANDBETWEEN(1,3)

This generates a random number between 1 and 3 every time a calculation is performed.
We will use these numbers to set the colours of the lights.

Step 3 - Format

Next add conditional formatting to all tree cells as follows.

Then "Manage Rules"...

If you also want to animate the star, add 3 conditional formats to the star cell as follows.
Select the star cell, then Conditional Formatting, New Rule…

Select "Use a formula…" and set the font colour based upon the value in a tree cell (say P8).

Repeat for the other two possible values with different font colours.

You can remove gridlines if you want a "cleaner" look for your sheet.

Step 4 - Animate

Press F9 to change the colours.  Or press and hold F9 to animate.

You can download the file with instructions under downloads.

https://www.how2excel.com/en/downloads-2/

Enjoy!