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 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 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 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 as;

=(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, formula is used to calculate CAPM excel as follows:

=(E4+E8*(E6-E4))
CAPM Formula Excel

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 accepted otherwise rejected.

This article will identify and use the CAPM Calculator Excel as 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 desire 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 desire values into the respective fields as shown in the above example.

Download template file of 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 as risk-averse.
  • A higher expected return would be required by the investor in terms of risk premium to take an additional risk for.
  • It is assumed that there is no cost of trading, regulations, or taxes.

What is difference between cost of capital, WACC and CAPM cost of equity

The cost of equity capital is calculated with CAPM formula. WACC equation highlights the value of the cost of equity plus the cost of debt securities. Therefore, CAPM calculates the cost of equity capital that 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 WACC equation for equity investment appraisal purposes.

Conclusion

The cost of equity is an opportunity cost that investors bear additional risk to attain 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.

How to Calculate WACC in Excel, Step by Step Guide

Calculate WACC in Excel

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 sum of cost of debt, cost of equity, and preferred stock values of 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

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 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 corporate tax rate is 25%.

WACC Calculation in Excel Format

Let’s, divide the above solution into three simple steps to understand it more precise and effectively.

Step 1

So, first is to put all the desire data into the following given format such as 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 weightage of both equity and debt values. Hence, the following image is showing calculation of weightage of both stocks.

WACC Weightage calculation

Step 3

In the last and most importantly, we need to use the WACC formula excel as shown in the following image.

WACC Formula in Excel Sheet
=((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 WACC calculator in Excel sheet by clicking on the button or use calculator online.