How to Calculate Cost of Funds in Excel

This guide will discuss how to calculate the cost of funds in Excel.

The cost of funds is most often used in banking, finance, and companies. And most companies will calculate their cost of funds to know how much they need to earn to keep the investment worthwhile.

Furthermore, financial institutions calculate the costs of funds to identify how much money they need to acquire funds to lend or loan to businesses.

So the cost of funds refers to the rate of interest financial institutions and banks pay on their debt or the money they borrowed to operate their business. Basically, it is an interest rate charged to obtain or get funds.

Essentially, the cost of funds is a total of all the blended costs of all sources of funds. And the formula for the costs of funds is the weighted average cost of capital or WACC. 

The weighted average cost of capital is obtained by multiplying the proportion of each fund source by its price and adding the results.

Let’s take a scenario.

Suppose you are an employee in a financial institution. And you were tasked to calculate the cost of funds for the funds a certain company borrowed. Although you can calculate WACC manually, utilizing Excel to calculate it would be easier and have a lesser chance of error. 

Great! Now let’s discuss a real example of calculating the cost of funds in Excel.

A Real Example of Calculating Cost of Funds in Excel

Firstly, we need to prepare our data set by inputting all the needed components to calculate the weighted average cost of capital or WACC. So this would be the corporate tax rate, long-term debt, preferred stock, common stock, and total debt and equity. 

So the formula for calculating the WACC is the cost of funds = LTP + PSP + CSP. Whereas LTP refers to long-term debt proportion, PSP is the proportion of preferred stock, and CSP refers to the proportion of common stock.

Long-term debt is a liability of a company. And the preferred stock of a company refers to an aggregation of the aspects the common stock does not contain. Lastly, common stock refers to security, representing ownership in a company or corporation.

And this is what our Excel sheet would most likely look like once we input all the necessary components. 

Secondly, we need to obtain the after-tax rate, which is done by the formula of =1 - corporate tax rate. Then, we will calculate the proportion of the long-term debt, preferred stock, and common stock. Lastly, we will calculate the cost of funds using the WACC formula. 

And this is our final output. 

You can make your own copy of the spreadsheet above using the link attached below. 

Awesome! Let’s finally discuss the steps of how to calculate the cost of funds in Excel.

How to Calculate Cost of Funds in Excel

In this section, we will explain the step-by-step process of how to calculate the cost of funds in Excel.

1. Firstly, we need to prepare the data set. So we will create a table containing the necessary information we need. For instance, the corporate tax rate, after-tax rate, long-term debt, preferred stock, common stock, and total debt and equity.

Moreover, we will prepare a table below where we will input the WACC calculations. So this will contain the proportion of the long-term debt, preferred stock, and common stock. Lastly, it will have a row for the cost of funds result. 

Preparing the data set

2. Secondly, we will calculate the after-tax rate. So input the formula =1 - corporate tax rate. In this case, our formula will be “= 1-A2”.

Calculating the after tax rate

3. Thirdly, drag down to copy the formula to the rest of the after-tax rate rows.

Calculating the rest of the after tax rate

4. Next, we will calculate the long-term debt proportion. For example, our interest rate would be 8%. To calculate the long-term debt proportion, input the formula “=(C2/F2)*B2*8%”.

Calculate Cost of Funds in Excel

5. Then, it’s time to calculate the preferred stock proportion. In this case, let’s say our percentage rate for our preferred stock is 2%. So we will input the formula “=(D2/F2)*2%”.

Calculate Cost of Funds in Excel

6. Next, let’s calculate the common stock proportion. For instance, our minimum amount of profit return for our data set is 10%. To calculate the common stock proportion, input the formula “=(E2/F2)*10%”.

Calculate Cost of Funds in Excel

7. Finally, it’s time to calculate the cost of funds since we have obtained the proportions of all needed components. Firstly, we will add all the proportions. So type the formula “=SUM(B6:B8)”. And press the Enter key to return the result of the cost of funds.

Calculate Cost of Funds in Excel

8. Additionally, let’s change the format of our results, so they return as percentages. Firstly, select the three components and right-click. Then, select Number Format.

Calculate Cost of Funds in Excel

9. In the Number Format window, go to Percentage. And select 1 in the Decimal places. Lastly, click OK to apply changes.

Calculate Cost of Funds in Excel

10. And tada! We have successfully calculated the cost of funds in Excel.

Calculate Cost of Funds in Excel

 

That’s pretty much it! Wasn’t it quite easy and simple? Now you can calculate the cost of funds in Excel whenever you need to. 

Are you interested in learning more about what Excel can do? You can now use the various other Microsoft Excel formulas available to create great worksheets that work for you. Make sure to subscribe to our newsletter to be the first to know about the latest guides and tutorials from us.

Get emails from us about Google Sheets.

Our goal this year is to create lots of rich, bite-sized tutorials for Google Sheets users like you. If you liked this one, you'll love what we are working on! Readers receive ✨ early access ✨ to new content.

 

0 Shares:
Leave a Reply

Your email address will not be published. Required fields are marked *

You May Also Like