Cost of funds is a term of finance and banking. Many times, financial institutions have to calculate the cost of funds for their working purposes. This calculation becomes much easier if they perform it in Excel. In this article, we will show you how to calculate cost of funds in Excel.`
How to Calculate Cost of Funds in Excel: Step-by-Step Procedures
First of all, we will describe what the cost of funds is. The cost of funds is the interest rate that banks and financial institutions pay on the money they utilize for their operations. The institutions spend this money to raise funds for their operations. In other words, it is a fee which is given to the money depositor for deposit their money in their institutions. Generally, this cost can be calculated manually. You can determine the cost of funds by using the following formula.
Where,
LTP = Long Term Debt Proportion.
PSP = Proportion of Preferred Stock and.
CSP = Proportion for Common Stock.
You can also calculate the cost of funds in Excel. In this article, you will see the step-by-step procedures of how to calculate the cost of funds in Excel.
Step 1: Preparing Data Set
First of all, we will prepare our data set to calculate the cost of funds.
- In the beginning, we will use the following data set for our working purpose.
- In our data set, we will input values for Corporate Tax Rate, Long Term Debt, Preferred Stock, Common Stock, and Total Debt and Equity.
- We will find out the values of other expressions through calculation.
Step 2: Finding out After Tax Rate
In our second step, we will find out the after-tax rate. We can find out this tax rate through the following procedure.
- First of all, apply the following formula in cell C6 to find out the after-tax rate for the corporate tax rate of 32%.
=1-B5
- Then, hit Enter to get the value of the after-tax rate.
- Lastly, use AutoFill to find out the after-tax rate for the lower cells of our data set.
Step 3: Calculating Proportion for Long Term Debt
Long-term debts are liabilities for a company. The company has a time limit of more than one year to repay these debts. In our example, the long-term debt for the first case is $90,000. Also, an unchanged interest rate of 7%. To calculate the proportion for long-term debt, see the below-given method.
- First of all, write the following formula in the formula bar for cell C11.
=(D5/G5)*C5*7%
- Then, after pressing Enter, you will get the long-term debt proportion for the information given in row 5 which is 2%.
Step 4: Determining Proportion of Preferred Stock
Preferred stock for a company or institution is an aggregation of one or more aspects that common stock does not have. When it comes to paying the percentage of the stocks, preferred stockholders are paid before common stockholders. In our procedure, the preferred stock for the first case is $68,000 with a percentage rate of 2.5%. In the following discussion, we will describe the whole procedure to determine its proportion.
- Firstly, use the following formula in cell C12 to determine the proportion of preferred stock.
=(E5/G5)*2.5%
- Then, press Enter and get the value of the proportion for our first case which is 0.5%.
Step 5: Finding out Proportion of Common Stock
We have discussed common stock and preferred stock in our previous step. Now, we are going to calculate the proportion of common stock. In the first case of our dataset, the amount of common stock is $180,000 with a minimum amount of profit return of 12%. Go through the following points for a better understanding.
- First of all, in cell C13, type the following formula.
=(F5/G5)*10.5%
- Then, select Enter for the value to appear in the cell which is 4%.
Step 6: Adding up All Proportions to Calculate Cost of Funds
Finally, we have calculated all the required proportions for our procedure. Now, we will find out the cost of funds through these values.
- First of all, we have to add all of the proportions to calculate the cost of funds.
- For that, type the following formula for the SUM function in cell C14.
=SUM(C11:C13)
- Finally, after pressing Enter, we will get the cost of funds for the first example of our data set which is 1%.
Read More:Â How to Calculate Production Cost in Excel
Step 7: Showing Final Result
In those previous steps, you can see the cost of funds has been calculated for the first time. Similarly, you can determine the cost of funds for the following case by repeating the above steps.
- For example, you can see the calculated cost of funds for the last case of our data set which is 3%.
Importance of Cost of Funds
From the above discussion, you will find the procedures to calculate the cost of funds in Excel. But why is this term important? Why do financial institutions need to calculate them? We will try to answer these questions in the following.
- If the cost of funds is higher, the financial institution will impose more interest on loan borrowers to compensate for the cost.
- If the cost of funds is lower, then the institutions can give loans at a lower interest rate.
- So, the institutions try to minimize their cost of funds as low as possible to get a maximum profit. The loan borrower has to pay less interest on their loan if the cost of funds is minimized.
Download Practice Workbook
You can download the free Excel workbook here and practice on your own.
Conclusion
That’s the end of this article. I hope you find this article helpful. After reading the above description, you will be able to calculate the cost of funds in Excel. Please share any further queries or recommendations with us in the comments section below.
