Site icon Pro Hustle

Basic Financial Formulas and How To Use Them for Calculations Using Google Sheets

Financial Calculations with Google Sheets

We looked at important financial concepts that you as an entrepreneur should know in the last article. Now we are going to learn some useful financial calculations that will come handy to make decisions regarding how to effectively manage your finances.

Before moving on, let’s see how this article is organized. So, you can have a clear idea about what we will be discussing.

  1. Interest
    -Simple Interest
    -Compound Interest
  2. Present Value
  3. Future Value
  4. Net Income
  5. Break Even Point
  6. Net Profit Margin
  7. Share Price
  8. Debt Ratio
    -Long-term Debt
    -Short-term Debt

Interest

If you plan to take a loan or deposit money in to bank then these financial calculations are important to determine what kind of income/expense you will be making at the end of a paying cycle. They are also important to evaluate different investment opportunities. You can consider bank’s interest income as the opportunity cost of investing/spending your savings on something else. In order to consider an opportunity as something you should pursue then that should at least yield greater ROI (Return on Investment) than what you would have received if you deposited that money in the bank.

Simple Interest

We briefly did a simple calculation on Simple Interest in our previous article and understood what it means by the phrase “Simple Interest” and why we call it “Simple”. To brush up your memory again, lets briefly see what we discussed earlier.
Simple interest is a way of calculating the interest on the principal amount. It is calculated by multiplying the Principal amount by the interest rate each year. The key factor of this method of interest calculation is that the interest is calculated on the “initial” principal amount. And each year, the interest is calculated on the initial deposit or borrowed amount and the interest is not added to the principal amount each year.

Compound Interest

Compound interest too, is calculated by multiplying the Principal amount by the interest rate. However, under the Compound interest method, the interest of each year is added to the Principal amount. And in the next year, you calculate the interest on the Principal + the interest of the previous year.

The key difference between Simple interest and Compound Interest is that under the Compound interest method, you get interest on interest, something that you don’t get with Simple interest. Now Why is this happening? Simple. It is because under the Compound interest, we add the interest of the prior period to the principal amount before proceed to calculate interest income for the current period. So, each year, automatically, we calculate the interest on the previous year’s Principal + Interest as well.

Interest Calculations

Now in the previous article, all we did was a basic and a very simplistic presentation and calculation of both the simple and compound interests. Although it was simple, you saw clearly that the longer the time periods and bigger the amounts get, harder the calculations become.
However today, we are going to look at a few mathematical formulas that will allow us to calculate the compound and simple interests easily and swiftly. Let us take a loot at these formulas now. First, Simple Interest formula.

A = P (1 + rt)

A = Total Accrued Amount (principal + interest)
P = Initial Principal Amount
r = Annual Interest Rate
t = Time. or by the annum the interest is paid.

As you can see, the Simple interest Calculation is straightforward. You just calculate yearly interest amount and add it to Principle to get the total, A = P + I = P(1 + rt). Here the yearly interest will be same every year if the interest doesn’t change overtime. When you add this stable interest income to principle amount you initially invested you get the total accrued amount.

Now let us take a look at the Compound Interest formula. And this can be a little bit tricky. But don’t worry. We got your back and once you understand what the formula means, it will be very easy for you to use in practical situations.

A = P (1 + r/n )nt

A = Total Accrued Amount (principal + interest)
P = Initial Principal Balance
r = Interest Rate
n = Number of times the Interest is calculated per time period
t = Number of years

Now if you look at it closely, you can see there are several new components in the formula of compound interest. But don’t worry, let’s go through them step by step and in the end, you will grasp the underlying rationale.

You can see that the interest rate is divided by the number of times the interest is calculated per time period as “r/n”. Now remember how the Compound interest work. It adds the interest of the time period to the principal amount. For an example, if the interest is calculated 2 times a year in 6-month periods, both in those 2 times, the interest is added to the principal. What this “r/n” do is to find the interest rate applicable for that number of times the interest rate is calculated per year, as interest rates are generally given for a year/annum.

The “nt” element outside the brackets, or the squaring function is a bit complicated. It means that you have to multiply the number of times the interest is calculated per year by the number of years. As you can now see, it numerically represents the number of times the interest is calculated throughout the time period. Why do we square the entire function by this you might ask? And the reason is quite simple, remember we learned earlier that Compound Interest calculate interest on interest over the years? that is why we square it.

There are other complex ways to explain this using complicated mathematics. But this will be more than enough for us. After all, we need this for our businesses, not to solve complex mathematical problems!

Simple Interest and Compound Interest Calculation with Google Sheets

Present Value

This calculation too seems like complicated at first glance but when you understand the concept behind it you will be able to see the rational for the formula. In our previous article on financial concepts, we understood what Time value of money is and what influence it. As the time moves on, the value of money you have slowly decreases. Therefore, the value of the money you have at the present have a higher value than in future.

The present value is the amount you would need to invest now, at a known interest and compounding rate, so that you have a specific amount of money at a specific point in the future.

Let’s now take a look at the formula and try to understand it.

Present Value = Sum of Future Cash Flows / (1 + r)n

r = Interest Rate
n = Number of Years

Sum of future cash flows means the total of cash inflows and outflow, of the net total value of all expected cash flows of the business. this is mainly an estimation that is derived on expectations, judgement, market trends and past experience. As u can see, we divide it by ( 1 + r )n. If we take the interest rate as 10%, of we right down that in a linear, decimal way, it can be rewritten as 0.1 so what this function does is, it adds the interest rate to 1 and then square it by the number of years.

In our example, notice that once u adds the interest rate 0.1 (10%) to 1, it becomes 1.1. and if we rewrite it in Percentage format, it becomes 110%. What does this mean? it’s a 10% increase. It is important to remember that this calculation only works if the interest rate remains unchanged, and in real life, it is like that. Interest Rates do not change regularly.

Now let’s look at the next part. We divide the Future Value, or the sum of total cash flows by (1 + r)n . why is that? because, over time, the time value of money decreases, and division is a form of repeated subtraction, meaning once u divide something, its decreases.

This simple explanation should be enough for you to understand the meaning of this formula. Always remember that it is very important to understand a formula than to memorize it. Only that way, you can use these financial formulas in real world applications.

Present Value calculation with Google Sheets

Future Value

Future value is the other side of the Present Value. We can simply define it as the value of a present cash flow at a given future day. Obviously, now you know that the future value of a present sum of cash is always lower than the present value. Let us now take a look at the formula to calculate it.

Future Value = Present Value ( 1 + r )n

r = Interest Rate
n = Number of Years

The formula is the exact opposite of the formula to calculate the Present Value. Same components, except their arrangement is different. If you understand the Present Value and the concept behind it, the idea behind this Future Value formula should be easy to grasp.

Future Value calculation with Google Sheets

Net Income

If you followed our article series on accounting, you must be familiar with this concept and calculation. Net Income is simply the excess amount we get once the revenue generated by our business exceed its expenditure.

Net Income = Total Revenue – Total Expenditure

Having a net income implies that the business has been managing its operations and resources well. While a net loss, which occurs when the total expenditure exceeds the total revenues implies that business is ineffective at managing its resources to generate income.

Break Even Point

Break Even Point is the point where the Total Revenue equals Total Profit of a Business. So, at this point, there is neither a Profit, nor a Loss. Which makes it an “Even” point. So why is the Break Even Point important? Simple. It’s important and even critical because it shows the minimum amount the business have to generate to not to make a loss. In other words, every dollar the business makes after the Break-Even point is profit!

Break Even Point = Fixed Cost / (Sales Price Per Unit – Variable Cost Per Unit)

Break Even Point = Fixed Cost / (Total Revenue – Total Variable Cost)

Here you can see that the Costs are categorized as Fixed and Variable, rather than the traditional way of categorizing them as Administrative, Financial, Distribution costs. This is because the Break-Even calculation is not a part of Financial Accounting, but a part of Cost Accounting. However, categorizing costs in your business as Fixed or Variable is not a difficult task.

Fixed costs are the costs that do not change with the level of production. Such as rent, loan interest, etc.

Variable costs are the costs that change along with the level of production of the business. Such as electricity, water, marketing costs, commissions, etc.

Chances are, in your small or medium scale business, there can’t be thousands of different kinds of expenses. You will have no trouble sorting these expenses out and categorizing them. and then apply them to the formula.

Break Even Point calculation in Google Sheets

Net Profit Margin

If you run a business or intent to do so, Profit Margin calculation is important in order to asses how much profit you make from a sale. Simply, Profit Margin is the portion of profit in the selling price. You can also say that it is the portion of profit in the total revenue.

Net Profit Margin = (Revenue – Cost) / Total Revenue

Net Profit Margin = (Selling Price – Unit Cost) / Selling Price

Note that the profit margin can be calculated in both ways. From total revenue viewpoint and from unit selling price aspect. Also, you must remember that even though the formula says “unit cost”, it necessarily doesn’t have to be a physical unit. If you run a service business, let’s say something like an online business, still this is applicable and all you got to do is to replace the unit cost with the cost of the service/job you provide.

Net Profit Margin calculation with Google Sheets

Share Price

We are not going to look at the calculation of Share price. Because stock valuation is very complicated and is a subject of its own. Needless to say, it is better to understand what share price is and the factors that influence it as if you ever think of investing in the share market and corporations, it will come in handy.

Share price is the price of the smallest unit of a company’s ownership. In other words, if you own a share, you own a portion of the ownership of the company. Shares have prices, and they change regularly, almost every day. sometimes they go incredibly high and sometimes they go unbelievably low. So, what is the reason for this price swings? The simple answer is, supply and demand. Or in other words, the demand for the shares of particular companies and the willingness of the current shareholders to sell the shares they have (supply).

Company/Industry News and other market information are the main causes that drive stock prices up and down. For an example, a new project or a hot new product that a company is going to launch can improve share valuation and interest in company shares. Result is prices going up!

Debt Ratio

If you have followed our Accounting article series, you should know by now that every Business has two main important components that is vital for its functioning. Equity and Debt. Although in general we assume that having a low debt is good for any business, for some businesses in some industries, having a higher debt amount is normal as such industries require it. For an example, the vehicle manufacturing and industrial equipment manufacturing industries.

As entrepreneurs, we must be able to clearly understand the debt of our business or in any business we are planning to invest. We must be able to identify the portion of Debt relatively to the Assets of the company.

Debt Ratio = Total Debt / Total Assets x 100

To calculate this ratio, simply take the total assets of the business and calculate the total long term and short-term liabilities of the business. then divide the total debt by the total asset. Multiplying it by 100 will tell you what percentage of debt is in the total assets.

Also, the Debt Ratio can be calculated for long term debt and short-term debt separately. If you want to calculate these ratios, simply replace the total debt and put in the short-term and long-term debts there.

Short Term Debt Ratio = Short Term Debt / Total Assets x 100

Long Term Debt Ratio = Long Term Debt / Total Assets x 100

Generally, the lower the amount, better it is for the business and the debt and debt repayment are lower. This will allow the business to take more debt if needed and by keeping the debt repayment low, more profits are distributed to the owners.

Debt Ratio calculation with Google Sheets

And that’s the end of our basic introduction to Business Finance and Accounting series. We hope these lessons will help you to better manage your business and personal finances.

PS: We recommend you to watch this YouTube playlist to learn more about this subject: LINK TO PLAYLIST.

Exit mobile version