Part one - we examine some of Excel’s financial functions. These functions take three or more arguments, but there are a few things you need to know to use them correctly. Most involve loans or calculations associated with the time value of money.
This session covered the following six functions
-
PV – Present Value of future regular cash outflow
-
PMT – periodic loan repayment calculation
-
CUMIPMT – cumulative interest – great for loan schedules
-
RATE – interest rate
-
FV – future value of regular investment
-
NPER – number of periods
The session finishes with a loan model that calculates the “missing” value for a loan scenario based on two out of three inputs. The IFERROR function is also discussed.
Part Two - I cover more financial functions. These are more related to comparing and analysing cash flows. I have also included a three 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
BONUS Video - see how to use goal seek and macros to solve the interest free period problem.
As with all my sessions, I will throw in a few other shortcuts along the way.