In July 2019 following on from the response and feedback from my June webinar, I covered more financial functions. These are more related to comparing and analysing cash flows. I have also included a couple of requested schedules.
This session will cover the following functions
NPV – Net Present Value of regular cashflows (learn to trick to using it correctly)
XNPV – Net Present Value of irregular cashflows
IRR – Internal Rate of Return of regular periodic cashflows
XIRR – Internal Rate of Return of irregular periodic cashflows
Discounted Payback period schedule
Flexible Loan schedule – handles lump sum payments
As with all my sessions, I will throw in a few other shortcuts along the way.
Let’s say you have an input range that covers the whole year. You only want users to make entries in the current month column. How can you limit the month entry? The answer is a custom Data Validation.
When developing budgets and financial models you may need the same rate/value (say debtor days) for 10 months of the year but need to adjust it for 2 months. It is usually December and January as they tend to be a little different due to the Christmas / New Year period. Here’s a couple of ways to handle exceptions in Excel.
Unfortunately Excel doesn’t have an ISDATE function. Excel’s macro language, VBA does, but there is no spreadsheet function that let’s you know if a cell contains a date. Well there is a partial workaround and you can also use VBA.
One of the cardinal rules of Excel is don’t key-in a value into a formula if that value could change. Tracking the value down could be problematic if you need to change the value. Tables can be the solution to avoiding keyed-in values.
In some cases you may have to make manual inputs across multiple cells that are spread across a sheet. Before making the entries you need to clear the existing entries. Creating a range name can make that process much quicker.
Inserting a Comment (renamed to Notes in newer versions of Excel) in the first row and then using Freeze Panes to keep that first row visible can cause issues displaying the Comment (Note). Here’s how to fix it.
When a list is filtered you are only seeing the rows that match the filter. The other rows are still there, just hidden. If you want to make the same entry in a group of filtered cells you can’t use the fill handle to drag and copy as you will overwrite the hidden rows. There is an easy way to do it.