Average Total Cost ATC Excel

How to calculate average total cost

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”.

Average Total Cost ATC Excel
Average Total Cost Formula in Excel
how to do multiply in Excel

How to do multiplication in Excel

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.

Multiply in excel
how to do subtraction in Excel

How to do subtraction or minus in Excel

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”.

Subtract multiple cells in Excel

NPV Calculation template

Calculate NPV using Excel (Discounting Formula)

NPV Calculation template
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

Note: The formula to calculate NPV with excel used different methods to measure net present value.

How to use Discounting Formula to calculate NPV with Excel

=NPV(rate,value1,[value2],…)

Rate = Annual discount rate for project investment

Value = Future discounting cash flows

Example

As shown in the above image where;

Discount rate = 10%

First, we need to calculate the factor value in order to find the Present Value (PV) of cashflows.

In this case, see the “Factor” column;

=1/(1+Discount rate)^Year

Then, find Present Value or PV cashflow by multiplying individual cashflow with discount factor, see “PV Cash Flow” Column;

=Cashflow x Factor

Finally, use the NPV formula in Excel sheet to calculate the net present value as follows:

=NPV(F5,D9:D13)-D8

Where,

F5 = Discount Rate

D9:D13 = Range of discounted cashflows

F8 = Initial project investment

NPV Calculation in Excel Template

Download the NPV Excel template file by click on the following button.

Calculator NPV & IRR

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).

NPV rule

The rule of net present value is dependable on the investor’s overall goal of wealth increment.

NPV > 0; accept the project

NPV < 0; reject the project

NPV calculation formula or equation

NPV Formula

Net present value = Σ – initial investment

Where:

Σ = sum of all discounted cash flows,

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.

How to calculate CAGR in Excel

CAGR Calculation in Excel RRI

CAGR or Compounded Annual Growth Rate is an investing and business-specific calculation to measure the annualized return of an investment over a given period of time. Here is an example to calculate CAGR in Excel.

CAGR formula Excel

In order to calculate the compound annual growth rate, we need to use the CAGR excel formula.

=RRI (nper, pv, fv)
CAGR Formula Excel RRI

Where,

RRI = This is referred to as the average required rate of interest for the growth of an investment.

Nper = This is states to the number of periods for an investment return (N)

Pv = This means a present value of an investment (P0 as initial investment value)

Fv = This mentions the future value of an investment (P1 as ending value)

CAGR calculation in Excel

For example, in order to calculate the CAGR in Excel, you need to put the requisite values as shown in the above CAGR excel format.

So, enter the following CAGR formula in the Excel sheet to calculate the value of the compound annual growth rate.

= RRI (E5,E4,E6)
CAGR Excel Formula RRI

This CAGR is calculated by using the RRI formula in 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

You can also use the online CAGR calculator excel for swift analysis and results.

Academic formula of CAGR

CAGR = (initial value / ending value) ^ (1 / time in years) – 1
Academic CAGR formula
Manual Formula CAGR in Excel

Where;

Cell E6 = Ending value

Cell E4 = Initial value

Cell E5 = Time period

Thus, we can use both generic formula and excel formula to calculate the CAGR value. Both calculations retrieve the same answer.

How to calculate CAGR in Excel with a negative number

GEOMEAN Excel function for CAGR

In order to calculate the CAGR in Excel with a negative number, you need to use the GEOMEAN formula in excel.

Firstly, and most importantly you need to calculate the percentage of change during each period from start to end as shown in column E.

Secondly, find out the growth factor by adding 1 + percentage of change as calculated in column F.

Lastly, use the GEOMEAN function in excel to calculate the CAGR value.

Formula of GEOMENA in Excel

=GEOMEAN (number1, number 2, number 3,….)
GEOMEAN function in Excel

number = This refers to the values for which you need to measure the average value of CAGR

The formula in cell F12 is:

= GEOMEAN (F6:F10) – 1
GEOMEAN Formula

Download CAGR Excel template using GEOMEAN

CAPM Calculator Excel, Formula, Free Template

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

CAPM assumptions

There are some basic assumptions of CAPM:

  • 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.

WACC Formula

How to Calculate WACC in Excel, Step by Step Guide

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
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.