I Average Total Cost is an accounting measure that shows the total cost (TC) per unit of output. To calculate the ATC, the total cost is divided by the total quantity produced.
Average total cost comprises both fixed and variable costs which are the expenses made to generate its output in terms of the final product. The VC (variable cost) decreases or increases based on a production level, whereas FC (fixed cost) remains persistent irrespective of the production level either increasing or decreasing.
Average total cost formula
Average Total Cost = Total cost / Output quantity
How to calculate average total cost in Excel
To calculate the ATC in Microsoft Excel, please follow the following simple steps:
Enter the data as per the below image in columns “Quantity Produced” and “Variable Cost” to calculate the “Total Variable Cost”.
Enter the “Fixed Cost” data.
Use the formula to calculate the total cost involved in an individual product produced as “Total Cost = Total Variable Cost + Fixed Cost”
Finally, calculate the “Average Total Cost” by using the average formula in Excel.
As in the example image, type the formula “=AVERAGE(G3:G5)” in the cell, where “G3:G5” shows the range of cells comprising the data of total cost.
Press Enter to find Average Total Cost, the outcome column shown in the cell where you entered the formula as in the example cell is “G6”.
In this post, I exhibited how you can multiply numbers by using the “*” operator in Microsoft Excel. This post led to huge queries from different platforms: How to do multiplication in excel? How do you multiply in excel? Yes, you can easily multiply the numbers in Excel.
A multiplication formula and sign in excel
The “multiplication” function is denoted as “*“. Excel is one of the most valuable applications that helps you to use this operator. The following example exhibited simple steps that can help you to multiply in Excel.
Step 1
Enter Product name
First, you need to enter your data as in this example “Product” name in column B. Click Cell “C4” and type Jeans and enter all your desired data.
Enter Price
Click Cell “D4” and type “Price” and enter individual product prices in column D.
Enter Quantity
Click Cell “E3” and type “Cost and enter the number of quantities sold in column E.
Multiplication in excel
Multiply cell in Excel
Calculate total revenue by using the multiply or multiplication * function in Excel
You can simply calculate the value of the total revenue you have earned by selling different numbers of products.
Click cell “F4” and type “=” and select cell “D4” and then enter multiplication function * and select E4.
Press enter to find an answer to the total revenue you have earned by selling products.
Multiplication in excel
Copy the formula of Cell “F4” by using Ctrl+C and after selecting your desired range, paste your formula by using the Ctrl+V shortcut key.
Using Microsoft Excel to calculate multiple numerical values that help in routine business. The “minus” or “subtraction” function in excel is used to calculate the remaining value for instance net profit after subtraction of product price from cost. Following are the easiest steps to use the subtraction or minus function in Excel.
Subtract cells in Excel
Step 1
Enter Product name
Click Cell “B3” and type “Product”. Press the key “Enter”. Click cell “B4” and type the product name such as “Jean” and enter all the product’s names in column B.
Enter Price
Click Cell “B3” and type “Price”. Press the key “Enter”. Click cell “C4” and type the price of the individual products in numerical form.
Enter cost
Click Cell “D3” and type “Cost”. Press the key “Enter”. Click cell “C4” and type the cost of an individual product in numerical form.
Step 2
Subtract cells in Excel
Calculate Profit margin by using the Subtraction or minus “-“ function in Excel
In order to calculate the profit you have earned against individual products sold, you need to calculate the profit by using the “Subtraction”, “Minus” or “Deduct” function in Microsoft Excel.
Press “=” and select cell “C4” (Price) and then enter “-“ and select “D4” (Cost). Press the key “Enter”.
NPV calculation in excel template using discounting cashflows
Definition of Net Present Value (NPV)
Net present value or NPV is a project appraisal method to measure the project profitability and acceptance/rejection decision by considering the present value of future cash flows. Calculate NPV using Excel provides a swift solution to measure the project feasibility of acceptance/rejection of the project.
Calculate net present value is the difference between present values of all cash inflows minus initial cash outlays.
The theoretical formula of NPV is;
NPV = Present value of cash inflows – Initial cash outlay
Calculate NPV with excel which provides you swift and easy way to measure project appraisal, acceptance/rejection decision making.
How to calculate net present value using a discount factor or WACC
The main criteria of choosing a project are to calculate net present value by using the discount factor. The risk and time value money (TVM) aspects are key to consider the project acceptance as it accounts for the cost of capital. The discount factor has recognized the cost of capital/WACC (weighted average cost of capital).
n = project lifetime in terms of months/years, and
i = discount factor or rate of return
Are npv and irr the same
NPV is a project appraisal method that can be measured by summing up all the present values of the future cash inflows and subtracting from the initial cash outlay. Thus, time value money, and risk adjustment are the key factors for decision making of project acceptance or nonacceptance.
Whereas, internal rate of return identifies the point where the NPV equivalents to zero at a specified discount rate. Thus, the rule of internal rate of return is to accept a project when the cost of capital is less than the IRR.
The decision rule for IRR
Cost of capital < IRR = accept the project
Cost of capital > IRR = reject the project
Hence, IRR predicts a positive NPV, when the cost of capital is less than the internal rate of return, so the project is valuable financing for investors.
Why NPV is better than IRR
NPV considers the future cash inflows that are reinvested at the minimum required rate of return, while IRR uses the cash inflows which would be reinvested at IRR. This proves that NPV is a more realistic approach to evaluate the project profitability when compared to the IRR.
Are NPV and DCF the same
Net present value equates to discounted present value of cash inflows less initial cash outlay. While DCF (discounted cash flows) are the values that are measured by using the discount factor.
What happens if npv is negative or zero
If NPV is negative means the IRR is less than the cost of capital, thus rejecting the project. You can only accept the project when the net present value is positive. Besides, the NPV is zero when the IRR equates with the cost of capital which means the project is acceptable.
Why is NPV the best solution in investment decisions in a corporation
A rational decision of project selection is based on future cash inflows by estimating the potential risk and time value of money. The NPV is useful to appraise the project in a corporation because it considers the time value of money and the risk-adjusted discounted value of future cash inflows.
Hence, it is a highly realistic and absolute way of selecting a project opportunity.
Which NPV is good when a comparison between NPV and IRR for mutually exclusive
A project with a positive NPV is good for investment opportunities. When considering the two or more mutually exclusive projects, NPV with a higher positive value must be accepted.
Conclusion
Corporations use different project appraisal methods to assess an investment opportunity and to choose the best of them. The time value of money and risk are determined when appraising project investment. NPV is a well-recognized investment selection process because it accounts for the TVM and potential risk scenarios.
Thus, NPV calculator with excel is best to help individuals or companies to measure the profitability of projects and acceptance/rejection decisions accordingly.
This CAGR is calculated by using the RRI formulain Excel from the given data of investment.
In this example,
Cell E4: This is presenting the initial value or present value (PV) of an investment as $10,000
Cell E5: This refers to a number of periods (nper) as 5 years
Cell E6: This states the ending value or future value (FV) of an investment as $13,500
Cell E7: Calculated filed
Result
Thus, the Compounded Annual Growth Rate (CAGR) or average rate of return of an investment will be 6.19%, if you invest money for 5 years, which will have $10,000 as an initial investment (present value) with the future value of $13,500.
Download CAGR calculator Excel template using RRI formula
How to calculate CAPM (capital assets pricing model) in Excel sheet
CAPM Calculator Excel / Formula
So, in order to calculate CAPM, you need to use the formula in excel;
=(Risk-free rate of interest/return (Rf) + Beta (β) x (Expected return of market E(Rm) – Risk-free rate of interest/return (Rf))
You can see in the excel sheet example
Where;
E4 = Risk-free rate of interest/return (Rf)
E6 = Beta (β)
E8 = Expected return of market E(Rm)
In this case, the formula is used to calculate CAPM excel as follows:
=(E4+E8*(E6-E4))
Definition of CAPM
CAPM or capital assets pricing model recognizes the relationship between expected return and risk to evaluate financial security. Thus, the capital assets pricing model determined the expected return on financial security investment. if the expected return is higher than the required rate; financial security is accepted otherwise rejected.
This article will identify and use the CAPM Calculator Excel as an example and template in simple steps.
Capital Asset Pricing Model (CAPM) Formula/Equation
CAPM equation
Where
ER = Expected return on a financial security investment
Rrf = Risk-free rate of return
β = Investment beta
Rm = Expected return on a market
(Rm – Rrf) = Market risk premium
Firstly, you need to put desired data into the columns such as Risk-free rate of interest/return (Rf); Beta (β); Expected return of market E(Rm) to calculate the CAPM.
Enter the desired values into the respective fields as shown in the above example.
Download the template file of the CAPM Excel Calculator
The security financial market is highly efficient, sophisticated, and competitive, where informed sellers and buyers function.
Markets are generally considered risk-averse.
A higher expected return would be required by the investor in terms of risk premium to take additional risk.
It is assumed that there is no cost of trading, regulations, or taxes.
What is the difference between the cost of capital, WACC, and CAPM cost of equity
The cost of equity capital is calculated with the CAPM formula. The WACC equation highlights the value of the cost of equity plus the cost of debt securities. Therefore, CAPM calculates the cost of equity capital which is essential to measure the value of WACC.
CAPM has a very important role to play in selecting the portfolio investment, which will be used to calculate the cost of equity, while the CAPM is used in the WACC equation for equity investment appraisal purposes.
Conclusion
The cost of equity is an opportunity cost in that investors bear the additional risk to attain the desired level of expected return.
Hence, the fund manager must ensure the equity stock return is higher than the cost of equity or at least equates to the cost of equity in order to satisfy the investors.
Weighted Average Cost of Capital – WACC Definition
A cost of capital must need to pay against corporate financing recognized weighted average cost of capital. Hence, WACC or Weighted Average Cost of Capital is a sum of all capital sources comprising debt, bonds, equity stocks, and preferred stocks.
Furthermore, it is the sum of the cost of debt, the cost of equity, and the preferred stock values of the corporation. There are two main sources of raising capital equity and debt.
Cost of Equity (Ke)
The cost of equity is a calculation of common stock to measure the proportion of equity shareholders’ investment in a project. Moreover, the cost of equity is the required rate of return on equity investment in the project.
Cost of Debt (Kd)
The cost of debt is the required rate of return on debt stock investment by the lenders.
What Weighted Average Cost of Capital Formula
Firstly and most essentially, we need to understand the theoretical formula of WACC which is calculated as follows:
WACC Formula Excel
Where;
E = Equity market value
D = Debt market value
Re = cost of equity
Rd = cost of debt
t = corporate taxation rate
E / (E+D)= Weightage of equity value
D / (E+D)= Weightage of debt value
How to Calculate WACC in Excel
Now, It will show you how to extract WACC value by using an Excel-based Calculator. For example, LuluLemon has a stock value comprised of both debt and equity capital. Thus, the total debt value is $1,000,000 acquired at a rate of 7%, and $2,500,000 as equity value at the rate of 4%, while the corporate tax rate is 25%.
WACC Calculation in Excel Format
Let’s, divide the above solution into three simple steps to understand it more precisely and effectively.
Step 1
So, first is to put all the desired data into the following given format total debt value, cost of debt (%), tax rate (%), total equity value, and cost of equity (%).
WACC Format
Step 2
Now, we need to calculate the total value of capital (debt+equity), and the weightage of both equity and debt values. Hence, the following image is showing the calculation of the weightage of both stocks.
WACC Weightage calculation
Step 3
Last and most importantly, we need to use the WACC formula in excel as shown in the following image.
=((E14*E22)+(E8*E20)*(1-E10))
Where,
E14 = Cost of Equity (%)
E22 = Weightage of Equity
E8 = Cost of Debt (%)
E20 = Weightage of Debt
E10 = Tax Rate (%)
WACC Calculation Excel Template
Also, you can download the template of the WACC calculator in Excel sheet by clicking on the button or using calculator online.
