Microsoft Excel, with its robust and powerful set of financial functions, is one such tool that can turn the complex into the manageable.
In this blog post, we're going to explore five key Excel functions that are especially useful for owners, executives and employees of small to medium-sized enterprises (SMEs) in South Africa. To make it easier to follow, we’ll be using bridge finance loans in our examples. These are interim funds that keep your operations running before long-term financing comes into play. If you're looking for more information on these loans, we shared a post answering what bridging finance is and how it can help your SME.
The PMT (Payment) function is used to calculate the monthly payment for a loan based on constant payments and a constant interest rate. It's an essential function for every director who needs to plan the monthly budget of their business and ensure that the loan repayment is included.
If your SME obtains a bridge loan of R500,000 at an annual interest rate of 8% for a term of 12 months, using the PMT formula like this: =PMT(8%/12, 12, -500000) would output R43,391.84. This is the monthly payment you must set aside in your budget for loan repayment.
The Future Value function can be used to project the remaining balance of a loan at a specific point in the future. This is particularly helpful for long-term and strategic financial planning. It allows you to anticipate the remaining liability on your loan at any given point in time, helping you plan for future repayments or to structure early repayments if necessary.
If you have a loan of R500,000, with monthly payments of R43,391.84 at an annual interest rate of 8%, and you want to know the outstanding balance 6 months into the term, your FV formula would look like this: =FV(8%/12, 6, -43391.84, 500000).
This will output R275,635.38. This means that, after making your regular monthly payments for 6 months, you would still owe R275,635.38 on your loan. Knowing this can help you make informed decisions about your business's financial planning, whether that's preparing for upcoming repayments or potentially refinancing or paying off your loan early.
The RATE function is used to calculate the interest rate per period of a loan. If you're not sure about the interest rate applied on your loan but know the loan amount, the monthly payment, and the loan period, this function becomes extremely useful. Understanding your loan's interest rate is crucial as it directly impacts your repayments and total debt.
Knowing that your loan amount is R500,000, your monthly payment is R43,391.84, and your loan period is 12 months, you could use the RATE function like this: =RATE(12, -43391.84, 500000)*12 which would output 0.08 or 8%. This function confirms the annual interest rate on your loan.
The NPER (Number of Periods) function is ideal for understanding how long it will take to pay off a loan, given the loan amount, interest rate, and monthly payments. This function is particularly useful when planning long-term financial strategies and helps to understand the timeline for freeing up future income currently committed to loan repayment.
If you know the loan amount is R500,000, the interest rate is 8%, and your monthly payment is R43,391.84, but want to confirm the repayment term, you could use the NPER function like this: =NPER(8%/12, -43391.84, 500000) which would output 12. This confirms that it will take 12 months to fully repay the loan.
The IPMT (Interest Payment) function helps determine the interest portion of a loan payment for a given period. This function provides insights into how much of your repayment is going towards interest versus the principal amount. It's valuable in financial planning, as it allows you to understand the cost of your loan over time.
If you want to find out the interest portion of your third payment, knowing that your monthly payment is R43,391.84 and the annual interest rate is 8%, you could use the IPMT function like this: =IPMT(8%/12, 3, 12, -500000) which would output R3,114.25. This shows that for your third monthly payment, R3,114.25 is the interest component.
Remember, knowledge is power. In this case, it's the power to turn numbers and rates into informed decisions and successful strategies.
Finance management might seem complex, but with tools like Excel and a little bit of learning, you're well on your way to becoming a master of your financial destiny. We’ve seen through the examples even an intimidating financing option like bridge finance can be made simple with these functions. Never underestimate the power of self-learning and the tools at hand but also, feel free to contact our Geddes team for help with your bridging finance and general financing needs.