where: R it = total return of a stock or portfolio i. The formula for calculating CAPM is as follow: Expected return on a security = Risk-free rate + Beta of the security * (Expected return on market – Risk-free rate)Expected Rate of Return. Where: E (Ri) is the expected return on the capital asset, Rf is the risk-free rate, E (Rm) is the expected return of the market, βi is the beta of the security i. General Motors Company has an expected return of 11%. , the rate at which investing in the market at. The next step is to calculate the percentage returns for both the stock and the index. A standardized beta compares the strength of the effect of each independent variable to the dependent variable. tsset period time variable: period, 1934m1 to 2019m6 delta: 1 month. 62%. Last Update: February 12, 2020. Calculating COE With Excel To calculate COE, first determine the market rate of return, the risk-free rate of return and the beta of the stock in question. Calculating beta with CAPM. Market Risk Premium = 15% – 4%; Market Risk Premium = 11% Most of the time, we need to base our expected return on the historical figures. One commonly used method to calculate Re is the Capital Asset Pricing Model (CAPM), which considers the risk-free rate, the market risk premium, and the company’s beta. Solution: Expected Rate of Return is calculated using the CAPM Formula given below. The expected return of General Motors is calculated by entering the formula "=C2+C3* (C4-C2)" into cell C5. Alpha Calculator. Alpha: What It Means in Investing, With Examples. Step 3: Calculate Nominal Risk-Free Rate. It is basically used to calculate the stock’s expected stock return. 12 trillion. The capital asset pricing model (CAPM) equation is. Step 1 – Download the Stock Prices & Index Data for the past 3 years. Where: w D – weight of debt. The company has stock shares that trade on the S&P 500. 5. To calculate COE, first determine the market rate of return, the risk-free rate of return and the beta of the stock in question. The following formula shows how to arrive at the risk-free rate of return: Risk Free Rate of Return Formula = (1+ Government Bond Rate)/ (1+Inflation Rate)-1. Face Value of Bond = $1,000; Current Market Price of Bond = $1,025; Annual Coupon Rate (%) = 6. . 98%. The required rate of return is also known as the minimum acceptable rate of return. Step 3: Insert the formula =1+ (1-B6)*B7 in cell B8 to calculate the denominator of the Unlevered Beta Formula. Rf = refers to the risk-free rate of interest. Real world example of the the CAPM using Microsoft Excel and regression analysis. By accumulating and tracking these over your certification cycle you can maintain your certification status with PMI. 65 = 0. Adjusted beta tends to estimate a security’s future beta. Upon multiplying the DPS of $2. Our findings justify the continued use of the CAPM by firms in spite of the mounting evidenceExample Calculation with Walmart. Calculating COE With Excel . Plugging. Where: NPV = Net Present Value. Therefore, our calculated MV of Debt is $ 573,441. Video of the Day Tip. Step 4: Next, calculate the expected rate of return by using risk-free rate (step. 3 * (11% – 4%) = 13. 1. Let’s break down the answer using the formula from above in the article: Expected return = Risk Free Rate + [Beta x Market Return Premium] Expected return = 2. Enter this into your spreadsheet in cell A4 as "=A1+ (A2_ (A3-A1))" to calculate the expected return for your investment. CAPM formula = B 1 + B 3 ∗ (B 2 − B 1) ext{B}_{1}+ ext{B}_{3}*( ext{B}_{2}- ext{B}_{1)} B 1 + B 3 ∗ (B 2 − B 1) 6: The expected return of an investment (ER i ext{ER}_ ext{i} ER i. . . The Capital Asset Pricing Model (CAPM) The cost of equity, Ke, comes from the CAPM. Calculate the individual present values. 94)))/. CAPM’s main strength – and its primary weakness – is that it assumes one single source of risk (i. Now by using the below formula, we can calculate Book Value Per Share: Book Value per Share = (Shareholder’s Equity – Preferred Equity) / Total Outstanding Common Shares. The total EPS over the holding period of 3 years is simply the EPS in Year 1, 2 and 3 added together. 038] + [540,000/ ( (1 + . To begin calculating NPV, it's important to calculate the individual present values for each period, such as each month, quarter or year. Calculating the correlation of stock A with stock B. CAPM CAPM as a Regression • The CAPM puts structure –i. Understand the Capital Asset Pricing Model. Ivan Kitov. CAPM Formula. 55 in Year 5 by (1 + 3%), we get $2. And the risk-free rate on a treasury bond is 2%. Beta will be calculated using the following formula: β = Covariance(R p,R m)/Variance(R p) Use the formula COVARIANCE. r e – cost of equity. 132, or 13. The Capital Asset Pricing Model. The formula for the capital asset pricing model is the risk free rate plus beta times the difference of the return on the market and the risk free rate. β can easily be calculated in Excel using the Slope function. CAPM: Here is the step by step approach for calculating Required Return. Abnormal returns can be. There are different ways to measure risk; the original CAPM defined risk in terms of volatility, as measured by the investment's beta coefficient. 5% from the stocks. The CAPM model can be used to calculate the cost of equity or cost of debt. The SLOPE Function [1] is categorized under Excel Statistical functions. Where = risk free return, = constant, = asset CAPM beta coefficient, = asset and market returns. Discount Rate is calculated using the formula given below. In this video, we go over how to calculate CAPM (Capital Asset Pricing Model). 80) Cost of Debt = $640,000. Consider the following information: Risk-free rate of return = 4%. Quick and easy way to calculate Alpha, Beta and R-SquaredWe show how to do a regression calculating alpha and beta in CAPMThe premise of the security market line (SML) is that the expected return of a security is a function of its systematic, or market, risk. Market Risk Premium: The market risk premium is the difference between the expected return on a market portfolio and the risk-free rate. #2 -By Slope Method in Excel. Step 2 – Sort the Dates & Adjusted Closing Prices Step 3 – Prepare a single sheet of Stock Prices Data &. Expected rate of return = Risk-free rate of return + β * (Benchmark return – Risk-free rate of return) = 4% + 1. Our certifications have different requirements based on the number of PDUs for maintenance. =COVARIANCE. First formula to calculate CAPM. What Are Alpha and Beta in Stock Market? How to Calculate Alpha and Beta in Excel (Step-by-Step) 1st Step: Prepare Outline and Dataset. The capital asset pricing model (CAPM) equation is. 3. To get readers comfortable using the attached WACC Calculator, we will calculate the WACC of Walmart as an example . So we have calculated the expected return using the CAPM approach as follows: E r = R f + β (R m – R f) E r = 4+1. Dividend Discount Model = Intrinsic Value = Sum of Present Value of Dividends + Present Value of Stock Sale Price. Step 3. 75 for MSFT at the time of writing. Risk Free Rate of Return Formula = (1+ Government Bond Rate)/ (1+Inflation Rate)-1. Country Default Spreads and Risk Premiums. A second part will then concentrate on the Capital-Asset-Pricing-Method (CAPM) and its assumptions, implications and drawbacks. The CAPM (Capital Asset Pricing Model) determines if an investment is reasonably priced. Interest is the cost of utilizing borrowed money. It takes inflation and returns into account and features particularly in capital budgeting and investment planning - there’s even a specific Excel function for it. In cell E2, enter the formula = (C2 / A2) to render the weight of the first investment. The equation that the model uses is based on the risk-free rate, the beta of the security, and the. The corresponding formulae are given below. Ability to Borrow at a Risk-Free Rate. The discount rate element. Examples of Beta Formula. The capital asset pricing model (CAPM) is used to calculate expected returns given the cost of capital and risk of assets. Then your percent change formula takes this form: = (C2-B2)/B2. Let's say that. For instance, type "Expected Return (Re)" in cell A1. Open Microsoft Excel. Pre-tax cost of equity = Post-tax cost of equity ÷ (1 – tax rate). Industry Name: Number of firms: Beta : D/E Ratio: Effective Tax rate: Unlevered beta: Cash/Firm value: Unlevered beta corrected for cash: HiLo Risk: Standard deviation of equity3. You need to provide the three inputs, i. So we simply take our current EPS of 9. For more information visit the Free Template. 25 x 7. Step 2. If we put the steps from the prior section together, the formula for calculating the ratio is as follows: Sharpe Ratio = (Rp − Rf) ÷ σp. The usage of the CAPM is based on the existence of certain assumptions. The screegrabs describe the formulae used in the spreadsheet. The beta estimate based purely on historical data – known as the unadjusted beta – is not a good indicator of the. " This creates a security market line with returns on the Y-axis and beta on the X-axis. The expected return of an asset can be calculated according to the following formula by the. Top 3 Formula to Calculate Beta. The calculation of the profit should be undertaken using investment appraisal techniques such as Net Present Value (“NPV”), Internal Rate of Return (“IRR”) and Payback period (“PB”). Then, divide the cash flow for the period by the result. * Now I indicate Stata that the time variable is period:. Capital Asset Pricing Model (CAPM) is a model that analysts use to measure risk and returns. The measurement of the cost of debt. Understand the concept of beta as a measure of systematic risk of a security. You need to provide the three inputs,. Put simply, CAPM estimates the price of a high-risk stock by linking the relationship between the risk of the stock and the expected return. Capital Asset Pricing Model - CAPM: The capital asset pricing model (CAPM) is a model that describes the relationship between systematic risk and expected return for assets, particularly stocks. Divide the first difference above by the second difference above. The Fama-French model aims to describe stock returns through three factors: (1) market risk, (2) the outperformance of small-cap companies relative to large-cap companies, and (3) the outperformance of high book-to-market value companies versus low. The capital market line is a tangent line and represents asset and investment mixtures. . CAPM comprises three core components: the risk-free return, the market risk premium, and Beta. Firstly and most essentially, we need to understand the theoretical formula of WACC which is calculated as follows: WACC Formula Excel. = the average return on the market. The Capital Asset Pricing Model (CAPM) helps to calculate investment risk and what return on investment an investor should expect. σp = Standard Deviation of Portfolio (Risk)15. Rf = the risk-free rate of return. The above calculation is done before the period under consideration starts, and it. risk premium = beta × (Rm - Rf), where: R – Expected rate of return of an asset or investment; Rf – Risk-free interest rate, typically taken as the yield on a long-term government bond in the country where the. Input Bond Assumptions in Excel. Find the risk free rate. The CAPM helps investors quantify the expected return after factoring in the risk associated with owning a company’s stock. 64% and the average US Treasury bill return of 3. Excel, Yahoo Finance, and 90 Day T-bill data from the US Federal Reserve to calculate the expected return of a s. 33% of the work has been completed. To obtain the correct format for calculation we must convert these prices into return percentages for both the index and the stock price. Using the CAPM formula we can find the expected return for an asset. The Capital Asset Pricing Model, or CAPM, is a basic theoretical model for determining the expected return on a security or portfolio. In finance, the capital asset pricing model (CAPM) is used to determine the appropriate required rate of return of an asset (or a portfolio). In t. The Capital Asset Pricing Model, or CAPM, calculates the value of a security based on the expected return relative to the risk investors incur by investing in that security. However, we observe that this stock has a positive intercept value after accounting for the risk-free rate. Video of the Day Tip. where: w n refers to the portfolio weight of each asset and E n its expected return. It suggests that not. The nominal interest rate is also referred to as the coupon rate for fixed-income investments. risk premium = beta × (Rm - Rf), where: R – Expected rate of return of an asset or investment; Rf – Risk-free interest rate, typically taken as the yield on a long-term government bond in the country where the. Step 1: We first need to calculate the debt-equity ratio. Here I have dispensed with all the constant terms, and also used the usual CAPM assumption that $epsilon$ represents idiosyncratic volatility, so $ ext{Cov}(epsilon_A, epsilon_B) = 0$, $ ext{Cov}(R_M, epsilon_A) = 0$ and $ ext{Cov}(R_M, epsilon_B) = 0$. The next step is to calculate the percentage returns for both the stock and the index. The CAPM analysis needs a series of steps to assess the risks of investing in a stock. Step 1 – Download the Stock Prices & Index Data for the past 3 years. Step 4: Press Enter to get the Result. In the context of portfolio management, alpha (α) is defined as the incremental returns from a portfolio of investments, typically consisting of equities, above a certain benchmark return. For a simple example calculation of the cost of equity using CAPM, use the assumptions listed below: Risk-Free Rate: 3%. ‘Cost of Equity Calculator (CAPM Model)’ calculates the cost of equity for a company using the formula stated in the Capital Asset Pricing Model. How to Calculate Alpha and Beta in Excel (Step-by-Step) Without any further delay, we will jump into the stepwise procedure to calculate the alpha and beta in Excel. $$ {\beta}_ {portfolio} = cov (R_p, R_m)/\sigma_m $$. Again, let us take an example. What is the Fama-French Three-factor Model? The Fama-French Three-factor Model is an extension of the Capital Asset Pricing Model (CAPM). n = number of periods included. Once you have found all of the above values, you can calculate the stock’s estimated return using the CAPM formula. read more or the government bonds. WACC formula of Company B = 5/6 * 0. Average weighted maturity = 37. Enter the Cost of Debt which is also a percentage value. 3 * (11% – 4%) = 13. Sharpe Ratio Formula. 7 / 20000) + 0. Enter this same formula in subsequent cells. To compute the portfolio weight of each investment, repeat the calculation in successive cells, dividing by the value in cell A2. WACC assumes the current capital. Last updated: July 14, 2023. Based on the capital asset pricing model, Phil should expect a rate of return of 8. By assessing the risk-adjusted return of an investment, investors can determine the cost of capital, which is the required return on investment a business needs to stay profitable. It is very easy and simple. Calculating the RRR involves discounting cash flows to arrive at the net present value (NPV) of an investment. a. CAPM can be used to evaluate the performance of many investments such funds. Equity risk premium = 5%. Dividend Payout Ratio =. Top 3 Formula to Calculate Beta. Market capitalization, or market cap, is the total value of a company’s shares held by all investors, including the. This CAPM-based approach will take the portfolio return, risk-free rate, beta, and market return, and calculate alpha from these elements: Here is a snippet of the alpha calculator template:Thse steps describe how you can calculate Alpha with Excel (there’s a link to download the tutorial spreadsheet at the bottom). The book Value per share of Jagriti Group of Companies is $47. The term CAPM stands for “Capital Asset Pricing Model” and is used to measure the cost of equity (ke), or expected rate of return, on a particular security or portfolio. #1- Covariance/Variance Method. The steps are; Gather the stock exchange and index data. Returns are all the earnings acquired after taxes but before interest is paid. * I create monthly variable for the months:. The Capital Asset Pricing Model (CAPM) helps to calculate investment risk and what return on investment an investor should expect. The greater the absolute value of the beta coefficient, the stronger the impact will be. • Systematic risk: (1) Cannot be diversified (2) Has to be hedged (3) In equilibrium it is compensated by a risk. Input the values into the CAPM formula. CAPM describes the relationship between risk and expected return and that is used in the pricing of risky securities. It will return the slope of the linear regression line through the data points in known_y’s and known_x’s. How To Calculate? Let us look at some of the formulas used to calculate risk premiums in the financial market. In the capital asset pricing model, cost of equity can be calculated as follows: Cost of Equity = Risk Free Rate + Equity. You can easily calculate the Risk Premium using the Formula in the template provided. Alpha Defined Alpha is computed in relation to the. 1%. The SML is a straight line that starts from the risk-free rate and has a positive slope equal to the market risk premium (the excess return of the market over the risk-free rate) multiplied by beta. The formula used is CAPM and WACC. Download the excel file here: How to calculate. The Capital Asset Pricingn Model (CAPM) is a formula used to determine whether investing in a partiular asset is profitable. 80% of your funds are invested in A. On the other hand, The effective interest rate takes the effect of compounding into account. The Fama-French Three-factor Model is an extension of the Capital Asset Pricing Model (CAPM). I review the definitions of realized return as well as normal and abnormal return. It is flawed as far as it relies on risk and returns distributions, the behavior of other investors, and some fundamentals of the market, that do not exist in the same form in reality. How to Calculate CAPM in Excel By C. The Capital Asset Pricing Model (CAPM) describes the relationship between the expected return and risk on any asset or portfolio. Follow me on Instagram: @simranpal_s1nghNo-commission platforms for investing:💵 Robinhood (use the link and we both get a free stock): to Calculate CAPM in Excel. Pre-conditions or assumptions for the applicability of the Capital Asset Pricing Model. Step 2. Course Curriculum: Objective. Investors always prefer to have the highest possible rate of return. Step 1: Theoretically RFR is risk free return is the interest rate what an investor expects with zero Risk. The risk-free rate refers to the implied yield on a risk-free investment, with the standard proxy being the 10-year. n = number of data values. Below is the expanded formula for the CAPM. Capital asset pricing model CAPM formula notation. Highlight cells D2 through D4 and then click on the "Insert" tab. Calculate the Beta coefficient. The formula for calculating CAPM is: Expected rate of return (Er) = Rf + β (Erm – Rf) Here is an explanation of each variable: Rf is the risk-free rate. 064; Required Rate of Return = 6. 2. 94)] = $573,427. Follow these steps to calculate β in Excel: Obtain the weekly prices of the stock; Obtain the weekly prices of the market index (i. CAPM Formula. Calculate the individual present values. A function is designed to return an output. There are two ways the SIM can be expressed, either in " raw returns " (aka ordinary returns) or in " excess returns . This value can then be compared with the market cap and. Asset pricing models consist of estimating asset expected return through its expected. This video shows how to calculate a company's cost of equity by using the Capital Asset Pricing Model (CAPM). E (Rm) = refers to the expected return of the. The CAPM (Capital Asset Pricing Model) determines if an investment is reasonably priced. Step 1: We first need to calculate the debt-equity ratio. Return on Invested Capital is calculated by taking into account the cost of the investment and the returns generated. Let’s say that ABC Corp. Solve for the asset return using the CAPM formula: Risk-free rate + (beta_(market return-risk-free rate). Estimating the Cost of Debt: YTM. CAPM Calculator In finance, the Capital Asset Pricing Model is used to describe the relationship between the risk of a security and its expected return. 21 today based on a price of $283. Us. The tenure of the project is eight years. Understand the concept of beta as a measure of systematic risk of a security. * I calculate the continuously compounded return from the simple returns:. Assess the risk-free rate of your asset. These are calculated and added to a new data frame called “capm. At the center of the CAPM is the concept of risk (volatility of returns) and reward (rate of returns). This CAPM calculator will allow you to quickly find the expected return on a stock using the CAPM. As an example, a company has a beta of 0. For equity, there is no such direct cost available. Alpha Calculator. A financial modeling tutorial on the CAPM model and how active portfolio managers use the Capital Asset Pricing Model to set expected return on a stock in th. CAPITAL ASSET PRICING MODEL Objectives: After reading this chapter, you should 1. The formula for CAPM: Ei = Rf + Bi (Em - Rf) Where Ei = expected return on an investment, Rf = the return on a risk-free asset such as US Treasury bills, Bi = beta of an investment, or the volatility of an investment relative to the overall market, and Em = the expected market return. 64% and the average US Treasury bill return of 3. In effect, the SML displays the expected return on an individual security at different levels of systematic risk. To calculate the weight of the initial investment, Investment Value, you need to divide Investment Value with Portfolio Value. CAPM, Diversification, & Using Excel to Calculate Betas The Capital Asset Pricing Model (CAPM ) • Equilibrium model that underlies all modern financial theory • Derived using principles of diversification with simplified assumptions • Markowitz, Sharpe, Lintner and Mossin are researchers credited with its development (early 1970’s) • Basic. In t. The table also includes standard deviation which is the next data component needed when building out the efficient frontier. The Capital Asset Pricing Model (CAPM) describes the relationship between the expected return and risk on any asset or portfolio. WACC Formula. Some other related topics you might be interested to explore are Cost of Debt and Cost of. Capital asset pricing model CAPM formula notation. Given the average S&P 500 return of 11. DCF Formula in Excel. Below is a screenshot of CFI’s WACC Calculator in Excel, which you can download for free in the form below. E (Ri) = the expected return on asset given its beta. This is an open-access Excel template in XLSX format that will be useful for anyone who wants to. Let me illustrate this with a simple example. To find the expected return, plug the variables into the CAPM equation: ra = rf + βa(rm - rf) For example, suppose you estimate that the S&P 500 index will rise 5 percent over the next three months, the risk-free rate for the quarter is 0. Step 1: Put the returns of your portfolio and the benchmark index into Excel, and calculate the average returns. Real world example of the the CAPM using Microsoft Excel and regression analysis. For news about the latest Excel for the web updates, visit the. Essentially, the model states that the intrinsic value of the company’s stock price equals the present value of the company’s. 132, or 13. Where: Rp = Expected Portfolio Return. The market rate of return is simply. The value of an investment is calculated by subtracting all current long-term liabilities, those due within the year, from the company. In this video, we look at the capital asset pricing model - CAPM for short. The market risk premium is part of the Capital Asset Pricing Model (CAPM. Step 2: Press Enter to get the Result. Then, calculate the beta of the portfolio using a weighted average of all the securities. There are several R code flows to calculate portfolio beta but first let’s have a look at the equation. The calculator uses the following formula to calculate the expected return of a security (or a portfolio): E(R i) = R f + [ E(R m) − R f] × β i. In this example we'll use the CAPM: Step 2: Calculate the cost of equity. In the example, this results in a CAPM of 0. CAPM is built on four major assumptions, including one that reflects an unrealistic real-world picture. 2nd Step: Define a Risk-Free Rate. 1 percent and the beta of the XYZ Mutual Fund is 0. = the return on risk free investments. 12. Download the Free Template. 5. The Capital Asset Pricing Model (CAPM) states that the expected return on an asset is related to its risk as measured by beta: E (Ri) = Rf + ßi * (E (Rm) – Rf) Or = Rf + ßi * ( risk premium) Where. To calculate the expected return on assets, you must utilize the CAPM formula: Expected return = risk-free rate + volatility/beta * (market return - risk-free rate). Microsoft Excel - Capital Asset Pricing Model (CAPM) Tutorial + Template. The Capital Asset Pricing Model (CAPM) measures the expected return from a given security. If you want to calculate your return for a specific time period rather than over the entire life of the portfolio, enter the value of the account on the starting date as the first contribution. 1. Asset Beta) is the beta of a company without the impact of debt. So, in order to calculate CAPM, you need to use the formula in excel;. 20 in year one and $1. 1. Calculate the cost of equity (Re). First, we have to calculate the cost of equity using the capital asset pricing model (CAPM). The firm is based in China. D = Debt market value. Calculate Portfolio Expected return. * I save the CETES dataset as. Put simply, CAPM estimates the price of a high-risk stock by linking the relationship between the risk of the stock and the expected return. Required Rate of Return = (2. Abnormal return, also known as “excess return,” refers to the unanticipated profits (or losses) generated by a security/stock. Convert the WACC to a decimal from a percentage and add it to one. R m: Expected Market return on the security (R m – R f): Market risk premium. It is based on the premise that the expected return of a trading position should be in line with the market's implicit risk premium. It facilitates the computation of security prices by considering the expected rate of return and the cost of capital. The formula for the CAPM is as follows: Where: Ra = Expected return of the security Rrf = Risk-free rate Ba = Beta (β) of the asset Rm = Expected return of the marketOne of the primary uses of CAPM is to value a business or a portfolio of securities. 1. Calculating the cost of equity using the CAPM. E r = R f + [B i x (R m – R rf)] E r = expected return of investment; R f = risk free rate; B i – beta of investment; R m = Expected return on the. Calculate Stock’s Beta using one of the two methods. The following entry explains a basic principle of finance, the so-called efficient frontier and thus serves as a gentle introduction into one area of finance: “portfolio theory” using R. The CAPM formula is as follows: Ke → Expected Return on Investment. In financial analysis, the SLOPE function can be used to calculate the beta of a stock. Use Excel, Yahoo Finance, and 90 Day T-bill data from the US Federal R. . The DDM is based on the assumption that the company’s dividends represent the company’s cash flows to its shareholders. How to calculate CAPM (capital assets pricing model) in Excel sheet. . I stumpled upon an exercise in an investments book: The data below describe a three-stock financial market that satisfies the single-index model. The Capital Asset Pricing Model (CAPM) provides a way to calculate the expected return of an investment based on the time value of money and the systematic risk of the asset. The formula is: K c = R f + beta x ( K m - R f ) where. How to Calculate CAPM? CAPM or Capital Asset Pricing Model helps to calculate the cost of equity for an investment. 1. Examples of Beta Formula. R i – Return Expectation of each scenario; P i – Probability of the return in that scenario; i – Possible Scenarios extending from 1 to n Examples of Expected Return Formula (With Excel Template) Let’s take an example to understand the calculation of the Expected Return formula in a better manner. 4. R f: Risk-free rate of return. Our findings justify the continued use of the CAPM by firms in spite of the mounting evidenceArchives Select Month. * I calculate the continuously compounded return from the simple returns:. Beta is a measure of the volatility , or systematic risk , of a security or a portfolio in comparison to the market as a whole. The total EPS over the holding period of 3 years is simply the EPS in Year 1, 2 and 3 added together. Calculating percent difference between 2 columns. To calculate the percent difference between two numbers correctly, carry out these steps. Step 1: Theoretically RFR is risk free return is the interest rate what an investor expects with zero Risk. In Excel, click Data Analysis on the Data tab, as shown above. Where = asset expected return, = expected risk free return, = asset CAPM beta coefficient, = market expected risk premium. S&P 500) and the return of risk-free securities. To calculate the cost of retained earnings, we can use the price of the stock, the dividend paid by the stock, and the capital gain also called the growth rate of the dividends paid by the stock. Method #1 – Dividend Discount Model.