How to use functions to calculate the value of loans, savings and investments

Welcome to your weekly Excel 3 Tip Friday, it’s just a short dose of Excel goodness before you start the weekend. Each week we’ll cover a tip, a shortcut and a function - making it super easy for you to get better at Excel.

Excel 3 Tip Friday returns this week, I’ve taken a few weeks off because of a new baby in the family, and I have to admit I don’t think my Excel tips are the best when I’ve only had 3 hours sleep!

If you find this useful or have requests or ideas for future editions, send me a message or comment at the bottom of this post! Now, on with the tips. 

A quick win you can learn today -

I find data validation extremely useful for creating drop down lists for data entry, and I usually store the list to be used in the drop down on a separate tab - often called ‘Settings’. 

But, what if that list is regularly updated? You’d need to update your data validation settings each time to keep the drop down up to date.

One way around this is to create a dynamic list by using a formatted table. To do this, create your list as a single column formatted table on a separate sheet. Note the name of the table, and of the column.

Now, in your data validation settings, change the list location to:

=INDIRECT(“table_name[column_header]”) replacing the table name and column header with your values. 

This way, you’ll never need to update your data validation settings and can just maintain a simple table instead.

A keyboard shortcut I'm adding to my list - 

Often it can be useful to quickly apply formatting to cells without having to click on the formatting menu. You can do this using several different keyboard shortcuts:

General number: Ctrl + Shift + ~

Currency: Ctrl + Shift + $

Percentage: Ctrl + Shift + %

A function I've found super useful this week - 

Excel has a whole host of financial functions that you can use to calculate the value of loans, savings and investments. One of these is FVSCHEDULE, which allows you to calculate the future value of an investment with a variable interest rate.

FVSCHEDULE has 2 components: the principal (or starting value), and secondly the schedule, which is simply a range containing the interest rates for each period. 

You can use this for your own finances to estimate what would happen to your investments if returns or interest rates changed over time.

Hope these were useful, until next time all the best. 

- Martyn

Edinburgh, Scotland

May 2018

Get More Exclusive Content

Sent Directly to your inbox with Excel 3 Tip Friday. A weekly dose of Functions shortcuts and tips for getting more out of Excel.

We won't send you spam. Unsubscribe at any time. Powered by ConvertKit