Personal Finance

The core goal of investing money is to generate value, whether in stocks or other financial assets such as bonds. In investing, two things are imperative: i) return on investment and ii) risk involved. One can not exist without the other; if you invest in the equity market, you are prone to market fluctuations daily, which creates a question on how much return you should get on the risk undertaken? You can find this answer using the Capital Asset Pricing Model or CAPM.  What is CAPM? Capital Asset Pricing Model is the calculation to determine how much return can be expected given a certain percentage of risk. In corporate finance, the CAPM formula holds significance as it helps find the cost of equity investment which is necessary for calculating the Weighted Average Cost of Capital (WACC). WACC helps in finding the real risk-return trade-off for a diversified portfolio.  The model was invented by the prominent economist and Nobel laureate William Sharpe in the early 1960s, which he later published in his book named Portfolio Theory and Capital Markets in 1970. He is also the inventor of the famous ‘Sharpe Ratio’. Each portfolio has a different cost of capital and degree of risk. As a result, one size doesn’t fit all and demands an efficient pricing model to gauge how much risk should be taken to gain a certain return and if it’s even worth it at all! CAPM model is the solution here.  The CAPM model considers the systematic risk of investment. If you wonder what systematic risk is, recall the market volatility when interest rate changes occur? In simple words, anything that affects the stock market becomes a systematic risk. With the inclusion of this market risk, the cost of equity returns becomes much more realistic.  These terms may sound complicated, but with some basic understanding, you can ace the calculation of the CAPM model. How to calculate CAPM? The CAPM formula is essential to ensure that the return on equity is at least equal to the cost involved. If it is less, the investment is a red flag and vice versa.  CAPM formula: Ra = (Rrf) + [βa * (Rm- Rrf) ] Whereas,  Ra = Expected return on equity asset Rrf = Risk-free rate of investment. Typically, the risk-free rate is equal to the 10-year US government bond. Though, the appropriate way is to consider country-specific bond rates. Ba = Beta (volatility) of the equity investment or stock. It shows the correlation of investment with the market fluctuations. For example, if a stock increases by 10% in reaction to market volatility, the Beta is said to be 1.1. The higher the Beta, the more the sensitivity to the market. If the Beta equals 1, the stock will behave the same as the market volatility; 1% move for 1% volatility. Beta can also be negative, which means that the stock value would reduce with regard to the market.  Rm – Rrf = Expected return of the market – Risk-free rate (Known as Risk Premium) Deduction of the risk-free rate of investment from the expected market return is also known as the market premium. The conscience here is that investors always prefer higher returns than the risk taken; they want a premium beyond the risk-free rate. The risk premium, in simple words, is investors’ compensation for undertaking the risk. We can also write the CAPM formula as: Expected return on equity = Risk-free rate (Beta of investment – Market premium)  Let’s understand the CAPM model better with the example.  CAPM example: The XYZ stock trades around Rs. 500 per share, and you expect to get a 5% return on this stock. It has a beta of 1.3, the market is likely to increase at the rate of 8%, and the risk-free rate is 3.5%. What is the cost of equity in this case or the return that can compensate you for the risk taken? Ra = (Rrf) + [ βa * (Rm- Rrf) ] Ra = 5 + [1.3 * (8 – 3.5)]  The return on your equity investment will be 10.85%.  Advantages of using CAPM  Below are the advantages of using CAPM.  The CAPM model considers the market risk, the systematic risk for investment, making the calculation realistic.  It helps calculate the cost of equity and, in turn, find the total cost of capital, aka WACC.  Compared to other models for calculating equity cost, such as the Dividend Growth Model (DGM), the application of CAPM is easier and has greater scalability.  Shortfalls of CAPM model Below are the disadvantages of using CAPM. CAPM is based on assumptions that there is perfect competition in the market and the market is highly efficient, which is not the case typically. There is always an opportunity to benefit from arbitrage trades.  Another assumption on which CAPM is based suggests that the investors are rational and averse to the risk. That is also a highly unrealistic expectation since investors often trade with emotions and are not rational.  The CAPM model doesn’t account for taxes an investor has to pay, which can sometimes reduce their returns. For example, In India, an investor has to pay a 10% long term capital gains tax on long term investment gains. This is not a part of calculating the cost of equity.  Apart from this, the other drawbacks include challenges to calculate Beta and potential frequent changes in the risk-free rate.  The bottom line Since its inception, the CAPM model has been used widely in the banking and finance industries. It helps in identifying assets worth investing in. It also played a vital role in bagging its inventor William Sharpe his joint Nobel prize. Yes, there are some assumptions used by this theory that has been a topic of debate for ages, but still, its application, without a doubt, holds significance. 

Whether you are an investor or a financial analyst, possessing extensive working knowledge of Excel and mastering foundational Excel functions can be invaluable. While it may seem nearly impossible to master every Excel feature, you must have a minimum knowledge of performing financial analysis using excel. If that is what you are looking for, you have come to the right place. Let us find out more about it here. Importance of Excel for Finance Professionals Excel is the choicest tool in the finance domain for financial analysis and insights, compiling non-numerical data and crunching numbers. Below mentioned are some points that highlight the importance of Excel for finance professionals: Allows Substantial Analysis Excel provides users aptitude to tackle intense qualitative analysis, which is a massive part of why this tool is invaluable in finance and accounting. You can input and interpret vast chunks of data and discern the direction of statistics and numbers as you continue manipulating them.  Will Move You Up the Ladder in the Corporate World Excel is the fundamental tool for most accounting companies, used to predict and facilitate a company’s growth. It also helps in decision-making and determining the system’s requirements and the changes that should be implemented.  Excel for Investors You can effortlessly keep a tab on your investment holdings with an Excel spreadsheet. The first step here is to decide the data that you would like to include, such as: Entry Date Size (the number of shares) The closing price for a specific date Difference between the entry and the closing price Percentage return Standard deviation Profit and loss for every periodic closing price In Excel, you can use a different sheet for every stock in your portfolio.  Top 10 Excel Functions for Finance Here are the top ten important Excel functions for finance that everybody should know: XNPV XNPV Formula = XNPV (discount_rate, cash_flows, dates) If your valuation analysis’s objective is to determine a company’s worth, you will have to figure out the Net Present Value (NPV) of a series of cash flows. XNPV considers specific dates for cash flows. Thus, it is more precise and useful. XIRR XIRR Formula: XIRR (cash flows, dates) This one is closely linked with XNPV but helps comprehend the Internal Rate of Return (IRR) for a series of cash flows provided on specific dates.  MIRR MIRR Formula: MIRR (cash flows, cost of borrowing, reinvestment rate) This is another version of IRR and is highly crucial for finance professionals. Here, the M denotes Modified, and this formula is used in case the cash from one investment is being invested in another investment.  PMT PMT Formula: PMT (rate, number of periods, present value) This is a common Excel function. With the help of an interest rate, a number of periods (months, years, etc.) and the present value of the loan, you can comprehend the regular payments. This formula comes up with a total payment, including principal and interest. IPMT IPMT Formula: IPMT (rate, current period #, total # of periods, present value) IPMT helps calculate the interest portion of fixed debt payments. This function works well along with the PMT function. By separating the interest payments in every period, you can come up with the principal amount in every period by considering the difference between IPMT and PMT.  EFFECT Formula EFFECT: EFFECT (interest rate, # of periods per year) This function helps you conclude the effective annual interest rate for non-annual compounding. This is a crucial function for you if you are involved with borrowing or lending.  DB DB Formula: DB (cost, salvage value, life/# of periods, current period) This formula can be used in Excel to determine your depreciation expense for each period RATE RATE Formula:  RATE (# of periods, coupon payment per period, price of a bond, the face value of a bond, type) The RATE function is generally used to evaluate the Yield to Maturity for a specific security. This function is useful for finding out the average annual rate of return earned from the purchase of a specific bond. FV FV Formula: FV (rate, # of periods, payments, starting value, type) If you wish to know the amount of money you will have in the future, this function is useful, given that you have a starting balance, a compounding interest rate and regular payments. SLOPE SLOPE Formula: SLOPE (dependent variable, independent variable) This function lets you calculate Beta, given that you have weekly returns for a specific stock and the index to which you want to compare it.  How to Make Investment Decisions Using Excel? With Excel, you can make investment decisions by: Calculating the Average Return and Standard Deviation for Stocks Let’s assume you wish to calculate the monthly returns for Amazon stocks. For this, you can use the following formula: Monthly return = Ending Price – Beginning Price / Beginning Price Calculating the Average Return and Standard Deviation for Portfolios Suppose your portfolio contains four different stocks: Amazon, CVS, Apple and Netflix. You can create an equally weighted portfolio, with each stock having 25% of the weight. You will use the following formula to do the calculation: Return = 0.25 x RAmazon + 0.25 x RCVS + 0.25 x RAaple + 0.25 x RNetflix Calculating the Stock’s Beta To calculate the Beta of a stock, you will need a monthly return for the market and the monthly return for that stock. This can be done by using a scatter plot to create a graph. For this: Open Excel Visit Insert tab From the Chart menu, select the scatter plot option By doing so, a chart will get inserted that will look like this: How Can Excel Template Help in Financial Analysis? The pre-made Excel templates save time and allow you to understand the financial performance in crucial areas. All you would have to do is choose a template, enter data, and it will automatically bring the results.  Conclusion Understanding Excel’s basic functionalities can help you generate quicker results and upgrade your professional

Walter Harvey Pvt Ltd had spent ₹12,00,000 to run its day-to-day operations, such as inventory management, transportation of raw material, and so on. This means they had a working capital of ₹12,00,000. It had ₹60,00,000 in net sales over the last 12 months.  But how do they determine if they’ve used their resources effectively? This is when the working capital turnover ratio helps! Thus, their working capital turnover ratio is ₹60,00,000/₹12,00,000 = 5.0. This means that every ₹1 of working capital produced ₹5 in revenue. This article talks about the working capital turnover ratio and what it conveys. What does it actually mean? To understand the working capital turnover ratio, you must know about working capital. Working capital is the difference between the current assets and the current liabilities of a company. This difference, when computed, measures the company’s liquidity, operational efficiency, and financial health in the near future (short-term).  If the company has considerable positive working capital, it most likely has sufficient funds to invest and grow. But if its working capital is negative (or low), then it may face troubles in its growth and settling its debts. Eventually, it might go bankrupt. Although positive working capital is good, it sometimes suggests that the company has excess inventory or has not invested excess cash. Now, coming to the working capital turnover ratio… The working capital turnover ratio estimates how efficiently a company utilizes its working capital to grow its sales. Since it is the ratio between the net sales and the average working capital, it is also called net sales-to-working capital. In other words, the working capital turnover ratio assesses the relationship between the funds used to run the operations of a company and the revenues it is earning to keep running its further day-to-day operations and remain profitable. Working capital turnover ratio formula The working capital turnover ratio can be calculated by dividing the average working capital by the net annual sales. So, the working capital turnover formula is​ Working Capital Turnover Ratio = Net Annual Sales/Average Working Capital. In the formula, the net annual sales are the sum of a company’s gross sales in a particular period, deducted by its returns, allowances, and discounts. In contrast, average working capital is the difference between average current assets and average current liabilities. Example of working capital turnover ratio Assume that you own an art supplies store. You sell products for art students, professional artists, art hobbyists, and art colleges.  In the course of the year, you made sales of ₹36,00,000. Moreover, your working capital was ₹7,00,000. You wonder if you’ve used your resources to their best or not. Therefore, you computed your store’s working capital turnover ratio to determine this.  To calculate it, you divided your net sales (₹36,00,000) by your average working capital (₹7,00,000). Thus, your working capital turnover ratio comes to be around 5 (approximately 5.143).  This means that for every ₹1 of your working capital, you made sales of nearly ₹5. In other words, every ₹1 that you invested into or allotted for your store’s operations made ₹5 of sales for you. What does it tell you? As mentioned above, the working capital turnover ratio denotes how efficiently a company is employing its working capital to carry on with its sales and growth. Moreover, to evaluate how effectively a company has been utilizing its working capital, analysts also compare its working capital ratio with those of other similar companies in the same industry. Then, they examine how the ratios have been fluctuating with time. Accordingly, they interpret the efficiency of the company. Nonetheless, these comparisons are not quite useful if working capital becomes negative. When the working capital turns negative, the turnover ratio also turns negative. You can also refer to the formula to understand this. A high (or positive) working capital turnover ratio suggests that the company is extremely effective in utilizing its short-term assets and liabilities to make sales. Put differently, it generates a higher rupee amount of sales for every one rupee of working capital it has used. The example above represents a high working capital turnover ratio. Therefore, a high working capital turnover ratio represents that a company is functioning smoothly and has no or limited requirements for additional funding. The cash inflow and outflow are regular, making the company flexible enough to spend its working capital for further expansion or inventory (production). Moreover, a company can also get a competitive advantage over other similar companies as it is an indicator of profitability. However, an extremely high working capital turnover ratio can sometimes also reflect a lack of capital to continue with sales and growth. Gradually, such a company can turn insolvent in the near future if it does not get additional funds. Apart from this, the working capital turnover ratio can be deceiving if the accounts payable of a company are very high. This would suggest that the company is facing trouble paying its bills as they remain unpaid. Such a company can even stop its operations due to a lack of funds. Contrarily, a low working capital turnover ratio indicates that a company has invested in a very high number of accounts receivable and inventory to continue with its sales. This can result in excessive bad debts or dead inventory in the near future. This can be understood with another example. Assume that your art supplies store made sales of ₹35,00,000. Moreover, you’ve acquired a new client, a prestigious art college. However, as your new client made several orders on credit, you accumulated a huge number of unpaid invoices. Therefore, your working capital came out to be ₹45,00,000. In this scenario, you computed your store’s working capital turnover ratio to determine your efficiency to use your working capital.  To calculate it, you divided your net sales (₹35,00,000) by your average working capital (₹45,00,000). Thus, your working capital turnover ratio comes to be 0.78, which is less than 1.  This means that for every ₹1 of your working capital, you

Microsoft Excel is an incredibly effective tool for managing data. It makes it simple for users to evaluate and interpret data. Microsoft Excel is unquestionably a strong and very popular programme utilized by practically every organization. Excel offers a wide variety of features that make working with data easier. One such Excel function is VLOOKUP. VLOOKUP serves as a search tool by scanning vertically over a table or spreadsheet for particular data. Let’s examine VLOOKUP in more detail. What is VLOOKUP in Excel? VLOOKUP in Excel stands for Vertical Lookup. VLOOKUP is a built-in Excel function that, as its name implies, enables you to find specific data by looking for it vertically across the page. It may appear difficult at first, but if you give it a try, you’ll see that it’s a simple to use and very helpful tool. It is arguably one of the most well-known features, for good and bad reasons. Let us now talk about some pros and cons of using VLOOKUP. PROS AND CONS OF VLOOKUP: On the plus side, VLOOKUP is simple to use and performs a highly valuable task. VLOOKUP can scan a table, locate a match, and deliver the right result, especially for beginners. Success with VLOOKUP is a must for moving from a novice Excel user to a proficient one.  The drawback of VLOOKUP is that it requires a complete table with lookup values in the first column, unlike INDEX, MATCH, or XLOOKUP. Because of this, using VLOOKUP with multiple criteria is challenging. Additionally, it’s simple to obtain false results because of the default matching behaviour. Understanding the fundamentals is the key to using VLOOKUP effectively. VLOOKUP Formula VLOOKUP formula or the syntax is as follows: =VLOOKUP (lookup_value, table_array, column_index_num, [range_lookup]) where, lookup_value – The value to search for in a table’s first column. table_array – The table from which a value should be retrieved. column_index_num – The table column from which a value should be retrieved. range_lookup – [optional] TRUE = approximate match (default). FALSE = exact match. Clearly, there are two possibilities if you want to be more specific: TRUE to find an approximate match.   FALSE to search for an exact match  In the absence of a particular command, TRUE is the default value. How To Find an Exact Match Using VLOOKUP? When utilizing VLOOKUP, an exact match is typically used, especially when the lookup value is a singular value. In this VLOOKUP example, an exact match is used because the lookup value is present in the table’s leftmost column. This time, the table array A:F contains the value 178462 or H2 in the leftmost column. Since the fourth column of the table contains the relevant value for the lookup, we enter 4 to instruct Excel to return the value from the fourth column of the table array in the same row. To tell Excel to return an exact match, we put FALSE as the fourth option. Excel returns Ahmedabad, which matches Client ID 178462 accurately. Excel will return #N/A to indicate that the lookup value could not be located if it is not included in the table’s leftmost column. How To Find an Approximate Match Using VLOOKUP? In order to discover the closest value that is less than the lookup value that we specify, Approximate Match searches for the next greatest value. Excel presumes that a lookup refers to an approximate match when no instruction is provided. Although it is generally less frequently used than exact match, it is nonetheless helpful when the lookup value is not present in the table array. For example, the table’s leftmost column does not include information about the 10,00,100 salary. When asking Excel to provide an approximate match in this scenario, TRUE is helpful. Because 10,00,100 is not available, it will return a figure lower than 10,00,100 (10,00,000in this case), which is 145301. Excel will return #N/A if we use FALSE (exact match in this case), as there is no value of 10,00,100 present in the leftmost column. How to Use VLOOKUP for Multiple Criteria? The fundamental principle of utilizing VLOOKUP in a situation with multiple criteria is to concatenate the various criteria so that you can utilize them as a single lookup value. In this case, the ampersand (&) will be used for concatenation. Therefore, before continuing with the lookup, we must create a helper column that joins the “Table_arrays.”   First, create a helper column. For this, let’s take an example. We will create a new column to combine the information from the “Name” and “City” columns. Insert a column after B which is the Helper Column (name it as per choice). Copy the formula (=A2&B2) to the remaining rows of the data after entering it in cell C2 of the worksheet. Now let’s follow these steps to understand the multiple criteria function better. To extract the data, insert the VLOOKUP function in the empty cell. 2. Enter the first argument which is “lookup_value”. This is the thing that you already know. Here “Name” and “City” is known while “Client ID” is not known. To alter the lookup value in the function, create an Excel VLOOKUP multiple criteria. The two criteria to include in the VLOOKUP should be concatenated to form the lookup value. In G2, H2, and I2, respectively, place the VLOOKUP formula, the first criterion, and the second criterion. H2&I2 should be the lookup value for the VLOOKUP. 3. Enter the second argument which is “table_array”. The place where you are seeking the data is the “table array.” It would be columns A through E if you were using a “regular” VLOOKUP. Columns C through E must be used in this scenario due to the assistance column. 4. Enter the third argument which is “col_index_number”. The column from which you want to return the result is indicated by the ‘col index number’. Our “table array” consists of three columns, and we want to return the Client ID, which is in column E (the third column). In our

Introduction: Suppose you are a Gym owner and have spent a great deal of money on Gym equipment. So you can only do this in case you are completely sure that the members of your Gym will eventually pay for the convenience of using the equipment purchased.  So as your business grows, you will recover your money with profit. The proper term here used is Accrued Income. Some other areas where you will come across this term are paying rent of your apartment, investing in mutual funds, etc. In this article, let us understand the accrued income, examples, and usages. What is Accrued Income? Accrued income refers to the money you have earned but has to be received in the future within the specified accounting period. Many individuals or firms are investing in mutual funds, or multiple companies decide to come together and pool their assets to invest in mutual funds in the hope of earning income in the possible future.  Some multiple firms and companies also bring about the income without actually having it, as it comes under the accrual accounting system. Now after understanding the accrued income meaning, let’s check out how to calculate it with the help of a formula. Formula: Accrued income is based upon the accounting equation, which says that the total sum of liabilities and the holder’s capital equals the total assets of the business firm. It will help if you read more about Profit & Loss A/c. For this, an accountant must pass the debits journal entry and read more accrued Income A/c and credit Income A/c. Calculation: There are three ways through which accrued income can take place in any business: #1 Rent Income Rent income also comes under the Accrued Income, although policies may vary. For example, Arya Real estate built a multi-story apartment and decided to give it on rent every quarter instead of taking the rent monthly. In this scenario, the rental income treatment is considered the Accrued Income. This is because it will generate revenue for two straight months simultaneously, but Arya Real Estate will receive its income in the Third month of the same quarter. #2 investment The retail investors and company also generate income on investment, which is yet to be received in the future. For example, Tesla invested $500,000 in Morgan Stanley bonds at a 4 percent interest rate on 1st January. Morgan Stanley will now pay interest of $10,000 to Tesla on 30th June and 31st January.  Tesla, which invests a sum of $500,000 at the end of the first month on 31st January, will not receive a sum of $1,667 in the same year. So till 30th June, the amount received by Tesla on 31st January will be an accrued income for the company as Tesla knows that they received a sum of $1,667 on their investment on Morgan Stanley bonds. Still, they will receive their accrued income in the future. #3 Income from Services It happens when the service provider agency provides its services to their respective clients, and the clients here promise the company to pay in the future for their services; in general terms, it is regarded as the accrued income. Now let’s further understand the concept with the help of an example of accrued income. Accrued Income Example. The Rana insurance company has rented Wilmer limited a $6,000 per month. On 31st December 2021, Rana Insurance showed the total outstanding rent of $12,000, which was received on 15th January 2022. Its Journal entry will be: #When income will be accounted for. 31st December 2021. Accrued Income A/C Debit $12,000 Income Account A/C Credit $12,000 Accrued Income accounted for #When Payment is received. Cash A/C Debit 15th January 2022 $12,000 Accrued Income A/C Credit $12,000 Limitations: Accrual accounting will be a demanding and hectic job for small businesses as hiring an expert to manage such core technical accounts is required. The lengthy reporting is the essential requirement for the Accrued Income, which is a complex and time-consuming process as it is opposed to accounting done on a cash basis. It generally happens when it comes to the splitting of the interest income and accrued income over a longer time. Accrual accounting does not let businesses escape from taxes by any means. So the thing here to keep in mind is that you should recognize the income before it is earned. You also must pay taxes on that certain income before it comes into your pocket. The Accrued Income will be very complicated and hazy if the accrual accounting paints an untrue picture of that particular firm in case the business has seasonal or cyclical earnings. If the accrual accounting is misused, the accrual accounting can create chances for executives and the management of the particular firm to report sales and revenue to gain advantages from the incentives and commission. Conclusion. Accrued income is essential for any Individual’s overall financial year, company, or financial balance sheet. So it is imperative to understand Accrued Income when it comes to the hard-earned money of your own business.  This article will help you with your long-term business and retail investment in the areas such as real estate, mutual funds, and other critical areas of investment you are looking for. Hyperlink https://www.investopedia.com/terms/a/accruedincome.asp https://www.wallstreetmojo.com/accrued-income/ https://cleartax.in/g/terms/accrued-income/amp

Pivot tables are one of the most powerful features in Microsoft Excel, and they’re great for analyzing data. You can use them to summarize large amounts of information into meaningful, concise reports. The best part is that pivot tables are easy to create. This article will help you learn more about pivot tables and how to create one in excel. What is a Pivot Table in Excel? A pivot table is a powerful Excel tool that helps organize, summarise, and analyze data. It is a dynamic table that can be updated automatically. They are not limited to any range of cells as they work with data sources like text, numbers, or formulas. Pivot tables do not change when you change the source data, so they are used in business, finance, and marketing, where changing the source data may happen frequently or even daily. Hence, it could be challenging to keep track of all these changes manually, which will take lots of time if you don’t have an automated solution like pivot tables that can do this job without wasting much time or effort.  What are the Uses of Pivot Tables? Pivot tables can be used to quickly summarise large amounts of information, and they’re a great way to compare different data sets. Here are some common uses of pivot tables: Data Analysis The pivot table is a powerful feature of Excel, which allows you to analyze your data and make sense of it. For example, if you have sales data and want to know how many sales were made by each employee concerning the product they sold, you can use the pivot table in excel. Data Exploration You can also look at different scenarios using Pivot Tables. For example, if one month has high sales while another has low sales, you can compare which items sell better than others by creating multiple reports based on this information. Data Summarisation You may need to summarise large amounts of data into a single table. A pivot table can make it easy for management or other people involved in decision-making processes, such as marketing agencies. For example, they may be interested in seeing how certain factors affect their business performance over time using visual representations like bar charts, pie charts, etc. This lets them view their information together rather than doing calculations. How to Use a Pivot Table in Excel? Organize your Data It can be overwhelming to understand where to start if you’re dealing with a large amount of data. It is important to organize your data beforehand to make the most of your pivot table. It can also be helpful to think through what you’re trying to learn from your data.  Is there a question that you want to be answered?  What do you hope to find out by looking at the data?  By answering these questions, you can determine the most important fields and where they should go in your pivot table. Insert the Pivot Table Choose the data you want to analyze. Next, click the pivot table icon in the ribbon and then select OK when prompted to select a cell location for your new pivot table Select a cell outside of any existing pivot tables on your worksheet, and then choose Insert > PivotTable > Choose Location on Current Sheet (or press Ctrl+Alt+M). Choose one cell in each range that you want to include in your pivot table: Setup the Pivot Table Field Once you have your data sorted, the next step is to set up your pivot table. To do so: Select the field you want as rows from the “Data” tab. Click on the “Insert” menu and then select PivotTable in it. In the Create PivotTable dialog box, click on the “Fields…” button under Choose fields for your report section in the first row of this dialog box to open the Field List window. Select any two columns from the list available there and drag them into Row Labels and Values lists, respectively, in the left side pane of the Field List window. Sort the Table To sort the table: Click the pivot table field you want to sort. Click the dropdown arrow at the right of your chosen field name and select Sort. Select either Ascending or Descending from the first dropdown menu that appears in order to determine whether you want your results arranged from A to Z (or lowest value to highest) or vice versa. Choose whether to sort by one column or multiple columns by selecting either “Single Column” or “Multiple Columns” in a subsequent dropdown menu. Finally, choose which column(s) you’d like to be sorted by clicking OK Filter the Data You can use filters to show or hide certain data. Let’s say you want to see all the sales that came in on Tuesday. When you start a pivot table, it will have all the days of the week represented as columns and categories. To filter for one day, click on the dropdown arrow next to “Tuesday” in your Pivot Table Field List and select “Show Values As” from the dropdown menu that appears. This will remove any rows from the report that don’t contain sales from Tuesday (or whatever day you’ve chosen). Edit the Data Values You can edit the data values with a pivot table by clicking on the dropdown menu on your pivot table and choosing the pivot table field. For this, select data values from this menu, and you’ll see a list of fields you have selected for your report. Click on one of these items, and Excel will expand your pivot table to show all of those fields instead of just one at a time. Learn How To Use VLook In Excel Final Thoughts Now that you understand how to use pivot tables in Excel, it’s time to put these skills into practice. The best way to learn how to create pivot tables is by trying it out yourself. The more you practice, the

An investor’s biggest concern is whether a company will yield profits or not. To find out if a company is worth taking a risk for, we take the help of various profit-measuring metrics.  These financial ratios calculate the overall financial health of a company. So, you can make informed investing decisions. In the following article, we will discuss Return on Capital Employed (ROCE) indicator and why you shouldn’t ignore it while investing. What is Meant By Return On Capital Employed? Before giving you the generic definition of ROCE & its importance, try to answer a fundamental investing question. How do you measure the profit-making capacity of a company? Checking how much revenue a company makes is a good start? Definitely! But that doesn’t tell us whether a company is profitable since there are always deductions. A company might be sustaining because of huge debts, for instance.  Moreover, it removes investors totally out of the picture! So what exactly does this tell you? As an investor, you would like to know how much profits it can make based on its capital. If it can make more money per rupee (of its capital), the more profits its investors will get.   ROCE is a financial ratio that tells how efficiently a company utilizes its capital to yield profits.  To find out the ROCE of a company, divide a company’s operating profits, which are also known as EBIT, by its capital employed. Formula of ROCE: (EBIT/ Total Capital) *100 Here, EBIT = total earnings before tax and interests. Total capital = Equity capital + debt capital  You can also obtain total capital by subtracting total liabilities from total assets.  There are many benefits of ROCE. Two major benefits of ROCE are: – It helps assess a company’s profitability and growth over time – ROCE helps in comparing two companies and which is better for investing Now, let’s discuss two of these in detail. Read About – Factors Affecting Stock Marketing Performance Assessing the Company’s Profitability and Growth One of the primary benefits of using ROCE is that it helps assess a company’s profitability and growth. While investing, investors, fund managers, and stakeholders use this indicator.  A high ROCE ratio is preferable, which indicates that a company is efficiently using its capital to generate profit. Conversely, a low ROCE signifies a company is over-leveraged or that its operations are not as efficient as they could be.  Let’s take a simple example to understand this concept- Assume that Company ABC has the following financial information for the year ended 31 December 2021: Total assets = Rs 5,000,000 Non-operating assets = Rs. 100,000 Current liabilities = Rs. 200,000 EBIT = Rs. 300,000 Tax rate = 20% ROCE is calculated as follows: Net Operating Profit After Tax (NOPAT) / Capital Employed Where, NOPAT = EBIT * (1 – Tax Rate) Employed Capital = Total Assets – Non-Operating Assets – Current Liabilities  Using the above information, we can calculate Company ABC’s ROCE as follows: NOPAT = EBIT * (1 – Tax Rate) = Rs 300,000 * (1 – 0.2) = Rs 2,40,0000 Capital Employed = Total Assets – Non-Operating Assets – Current Liabilities = Rs. 5,000,000 – Rs. 100,000 – Rs. 200,000 = Rs. 4,700,000 Return on Capital Employed = (Rs 2,40,000 / Rs 4700000)* 100 = 5.1% Therefore, Company ABC has a ROCE of 5.1%. The low percentage suggests that the company is over-leveraged and is not running its operation as it should, implying it could be a poor choice for investment. Comparative analysis of companies with different capital structures Another reason why ROCE is significant is that it lets you evaluate the profitability of two businesses. If both have the same ROCE, they make the same profit for each rupee of capital employed.  However, if one has a higher ROCE than the other, it is more profitable and generates greater profits for each rupee of capital employed. You can compare the ROCE of the companies to check which would be a better investment. Let’s see an example to understand this concept.  Suppose there are two companies: X and Z. Recently, both the firms made equal profits. Which company would you choose, X or Z?  Since we are short of information here, it is hard to tell.  Now, let’s say company X makes a profit of Rs. 200 on sales of Rs. 1,000, while Company Z makes Rs. 250 on Rs. 1,000 of sales. If we compare the two in terms of profitability, X has a 20% profit margin, while company Z has a 25% margin.  However, if we say X utilizes Rs. 500 of capital and B Rs. 1,000. And we found out X has a ROCE of 40% (200/500), whereas Z has a ROCE of 25% (250/1,000).  Based on ROE, which would you choose? X is a better investment because it will shell out more money. And which, if invested back into the company, will benefit the shareholders more. Limitations of Capital Employment Indicator There is no doubt that ROCE is one of the financial ratios to consider when investing. But it is not a perfect metric. No single metric guarantees that/this company is worth taking the risk. Though, ROCE can give you an idea, which you can verify with other metrics such as Return on Equity (ROE).  Some of the limitations of ROCE are- It varies as per the accounting policies. ROCE does provide correct information on the performance of companies with large cash reserves. The Key Takeaways ROCE is all about the profit-making capacity of a company on its capital. In simple terms, it tells investors how much profit a business makes for every rupee invested. Calculating ROCE is easy. You divide its total earnings before tax and interests by the sum of total equity and total debt.  The financial ratio has many benefits. From assessing the growth and profitability of a business to comparing two companies with different capital, ROCE is one of the most reliable performance indicators for investors. It also

A Guide to Calculate Variance in Excel Variance is an essential financial measurement that is used for all types of businesses. Variance can play an important part whether you are trading futures or want to understand the performance of different assets in your portfolio.  What is variance? Variance can be described as a measure to analyse how far numbers of a set are from the mean value. From a practical point of view, it shows how much something tends to alter. Hence, variance measures the variability in the data set. Mathematically, it is the average of differences(squared) from the mean value.  Let’s look at an example below to understand how you can use variance in your life. Suppose that there are 5 children in your locality. The ages of these children are 14, 10, 8, 6 and 2. The mean would be: 14 + 10 + 8 + 6 + 2 ————————— =  8                 5 Now that we’ve calculate mean, variance can be calculated using simple steps. Calculate the mean value of all the values i.e 8. Subtract the mean from each number to compute the differences between the mean value and the actual value. The differences for each of the items (14, 10, 8, 6 and 2) would be 6, 2, 0, (-2), and (-6). It’s time to find the square of each of the different values. These would be 36, 4, 0, 4, and 36. Now, calculate the average of all the squared differences. It would be: 36 + 4 + 0 + 4 + 36 ————————— = 16               5 The variance comes out to be 16 in this case. But what is the purpose of calculating variance? What does this 16 signify? Variance is used to compute differences in the data set. The smaller the number, the lesser the data set would spread. The larger the number, the more dispersions will be. The number 0 means there isn’t any variability in the data set. Learn Finance From Top 1% What are the advantages and disadvantages of calculating variance? Advantages: Variance gives a better insight into the groups covered in the data set, as seen in the above example. Variance treats all the differences/deviations from the mean as they are, even without paying attention to their directions. Variance is widely used in many fields, including finance, where investors can calculate risk. Disadvantages Data gets skewed when there are numbers far from the mean. Hence, sometimes, additional weight might be given to such numbers. Interpretation of variance is not always easy for everyone.  Standard deviation might be a better option than variance and is often used more than a variance. How to calculate variance in excel? To calculate variance in excel, you need to know the variance formula. Since there are inbuilt features, calculating variance in excel is an easy task.  Generally, variance is calculated for a sample and population. Let’s look at how to calculate the variance of a sample in excel. Calculating the variance of a sample Suppose you are interested in a rare animal found in America. You have collected 15 samples and computed their length in meters. Now, to calculate the variance of a sample, you’ll use the VAR.S function in excel. = VAR.S (value 1, value 2, value3, value4, ……..value 15) Let’s find out the variance. Step1. Enter your entire data in a single range of cells(all the values of 15 samples) in excel. Step2. For calculating variance, enter the formula:  Variance =VAR.S(A2:A15) Step3. The variance for your sample will be displayed in the next cell. Note: Three functions can be used for calculating the variance of a sample ( VAR, VAR.S, VARA). For VAR and VARA, excel version 2000-2019 is required. For VAR.S excel version 2010-19 is needed. Calculating the variance of a population Now let’s say instead of a sample, you’re interested in the entire population of the animal in the whole world. There are 100 species in total. You must keep in mind that calculating variance for a population requires a different formula from the sample one, i.e. VAR.P function. Now, to calculate the variance of a population, you’ll use the VAR.P function in excel. = VAR.P (value 1, value 2, value3, value4, ……..value 100) Let’s find out the variance. Step 1: Enter your entire data in a single range of cells(all the values of 100 samples) in excel. Step 2: For calculating variance, enter the formula:  Variance =VAR.P(A2:A16) Step 3: The variance for your population will be displayed in the cell. Note: Three functions can be used to calculate a sample’s variance ( VAR, VAR.S, VARA). For VAR and VARA, excel version 2000-2019 is required. For VAR.S, excel version 2010-19 is needed. Similarly, three functions can be used to calculate a population’s variance ( VARP, VAR.P, VARPA). Excel versions required are 2000-19 for VARP and VARPA and 2010-2019 for VAR.P. Importance of variance in finance Variance holds a special space in finance like any other field. It is used to compare the performance of all the assets in a portfolio to attain the best asset allocation where risk is minimised, and returns are maximised.  Let’s have a look at an example where our interest lies in the prices of the stocks and their returns.  We observed the stock’s prices and returns for 10 days. The stock prices were Rs 100, 120, 105, 110, 125, 130, 140, 135, 125 and 130 respectively. The returns as observed were 20.0%, -12.5%, 4.8%, 13.6%, 4.0%, 7.7%, -3.6%, -7.4% and 4.0%.  The variance would be 0.082, and the standard deviation (square of variance) would be 9.05% when we use the variance formula that we mentioned earlier. Variance for investors Variance assists investors in making investment decisions. Let’s say you are an investor with a high-risk tolerance. Then you’ll look for investments with high risk. High-risk investments usually have a high variance. Investments with low risk(low variance) are considered ideal for conservative investors. Variance explains or computes the risk associated with investments that whether a

Introduction Interest, which is one of the most commonly used financial terms is nothing but the cost of borrowing money. When one borrows money from a financial institution, they pay an annual interest in lieu of the money that is borrowed. Interest is generally expressed in percentages and it can either be simple or compounded. One of the major differences between simple and compound interest is the way in which interest builds up. Before diving into the details, let us first understand the meaning of simple and compound interests. What is a Simple Interest? Simple interest is the percentage that is charged on the principal sum of money that is lent or borrowed. Similar to this, when you deposit a particular amount in a bank, you can also earn interest. The concept of simple interest is widely used in many industries, including banking, mortgages, autos, and other financial institutions.  The formula used for calculating simple interest is as follows: SI = (P * R * T) / 100 Where,  SI = Simple Interest  P = Principal R = Rate of interest (expressed in percentage) T = Time duration (in months or years) If time, rate of interest and the principal amount is known, the simple interest can be calculated using this formula. To calculate the total amount (A): Total Amount (A) = Principal (P) + Interest (I) Where, Principal (P) – The amount that was first put in a bank or borrowed is known as the principle.  Rate of interest (R) – The interest that is charged on the principal amount. Time (T) – This is the time frame during which money is deposited or borrowed. Typically, time is described in terms of months or years.  Amount (A) – The total interest and principal for a specific time period are added to determine the amount. For Example,  If my friend deposited 100,000 rupees in a bank for 3 years and it accrued interest at a rate of 4% annually. Then,  Principal (P) = 100,000 Time (T) = 3 years Rate (R) = 4% p.a. The values in the simple interest formula should be substituted; SI  = (P * R * T) / 100        = (100000 * 4 * 3) / 100        = 12000 She earned an interest of 12000 rupees The total amount received by her would be  A = P + I     = 100,000 + 12,000     = 112,000 What is a Compound Interest? The interest that is calculated on both principal and previous interest and is compounded on a regular basis is known as compound interest. The interest is computed on a new principal every time. This principle is the cumulation of the accrued interest and the original principal. The formula for calculating the amount:  Compound interest can be calculated annually, semi-annually, quarterly, or even monthly. This is similar to how reinvested interest in an investment accelerates the growth of the principal over time. It is exactly what money does due to compound interest. Banks and other financial institutions only calculate the amount using compound interest. The total amount at the conclusion of the time period, which includes the principle and compound interest, is represented by the formula above. Moreover, by deducting the principal from this sum, we can get the compound interest. To compute compound interest: In the above expression, P is the principal amount r is the rate of interest(decimal) n is frequency or no. of  times the interest is compounded annually t is the overall tenure. For example,  If an investment yields a monthly compounded return of 4%. Let’s calculate the value after 5 years, on rupees 7,000 initial investment. Earnings of 4 percent compounded monthly are calculated using the formulas (r) = 0.04 and (n) = 12. The first investment or (P)=7000 and the time period or (t) = 5 Let us first calculate the value of A (the value after 5 years). Apply the formula now using the values that are known: A = P (1 + r / m)mt     = 7000 ( 1 + 0.04 / 12) 12 * 5     ~ 8547 Now, the compound interest can be calculated by subtracting the principal from the final amount CI= A – P CI= 8547 – 7000 CI= 1547 What is the power of compounding? The power of compounding is the process through which interest accrued and is added back to the principal amount over time, increasing the value of an investment. Investors use the concept of compound interest to leverage the power of compounding. Compound interest raises the potential interest for the subsequent cycle by earning interest on your principal amount and adding it back to the principal. The advantages of the ability of compounding are as follows: It assists in significantly increasing the principal amount over time. It enables investors to generate returns while also raising the return potential for the following cycle. When compared to simple interest, the power of compounding accelerates the rate of return on investments. Any financial instrument can use the power of compounding to boost its return potential. In-depth financial understanding is not necessary for successful implementation. Difference Between Simple Interest and Compound Interest? If put in the simplest way, simple interest is beneficial if you are borrowing money, whereas compound interest is helpful if you are investing. For borrowers like us, simple interest is preferred because you aren’t paying interest on interest. Simple interest makes paying off debt easier. Compound interest, on the other hand, can help you build wealth over time as the earnings retained here produce income. Another important point of distinction is that while the principal remains constant when computing simple interest, it varies for the calculation of compound interest. Conclusion Both simple and compound interests are widely used for interest calculations and are important to understand. While simple interest is common for taking short-term debts which last for less than a year, compound interest is mostly applied on loans having longer time periods. 

If you’re considering getting into the commercial real estate market, it’s a good idea to get familiarized with CMBSs. This post will give you a better understanding of what they are and how they can help you. Commercial mortgage-backed securities, or CMBS as they are popularly called, are fixed income securities with an underlying commercial real estate as a mortgage for the payment of coupons and principal invested. Unlike the traditional mortgage-backed securities, which have residential real estate as underlying mortgages, CMBS have commercial properties as the mortgage. Purpose The major purpose of CMBS, like MBS, is to provide liquidity options to commercial lenders. They allow the lenders to reduce the risk of their financing by allowing them to create derivatives of the loans into financial instruments and sell them out in the commercial market as investment opportunities. Meanwhile, it also gives investors the ability and risk appetite to invest in these instruments and generate regular cash flows through coupon payments. Retail investors can invest in these instruments through third-party products such as mutual funds and exchange-traded funds (ETFs) that specialize in investing in such products. Direct investments in such products are limited to ultra-high net worth individuals and family offices due to the large ticket size in these products. Structure The structure of CMBS works on the pooling of loans given by lenders for commercial properties. These loans are pooled together on the basis of their cash flow to create fixed-income securities that they can sell to investors. The securities are divided into multiple layers basis the risk that they carry in terms of credit and loss. The major classification of these securities include: Investment grade bonds – These are securities that carry lower risk in terms of credit and loss and thus have better credit ratings such as AAA, AA, and other higher ratings from credit rating agencies. These are considered safer investment opportunities for investors. Non-investment grade bonds – These are pooled commercial loan accounts that carry a higher risk of credit and loss compared with investment graded and thus are lower by credit rating agencies such as BB, B, etc. Since these carry higher risk, they are suitable for investors with a risk appetite. They will, however, have a higher coupon to compensate for the larger risk.  Unrated bonds – These bonds carry the highest risk in terms of credit and loss probability and thus are unrated by credit risk due to this potent danger. Investors only with the highest risk appetite and ability to bear the loss should invest in such bonds. Diagrammatic representation of CMBS   Benefits CMBS have multiple benefits across the value chain for borrowers, lenders and investors. Borrowers – Since the borrower is mortgaging their property for the loan, they can procure a lower interest on the monies. Further, they can also generate a higher value of loan basis the valuation of the underlying property. Further, in many cases, the upfront fees are also lower than traditional loans lowering the overall cost to the borrower. Lenders – The most significant benefit to the lender is that they can securitize the loan portfolio, thus improving the overall cash flow in their financial portfolio. They can also enhance their loan portfolio, thus improving their overall balance sheet size and revenue opportunities. Investors – It provides investors with an opportunity to invest in a fixed income portfolio of securities basis their risk-return appetite considering the wide type of bond tranches available in this segment. Drawbacks While CMBS loans are a suitable conduit for all parties in the CMBS ecosystem, there are also some drawbacks to these securities. High valuation – Since these bonds are pooled loan accounts, these are of high value, which is beyond the reach of individual investors who want to take exposure to these securities. Only ultra-high net worth individuals or family offices tend to have the monies to directly invest in these securities, limiting the investment opportunities for investors. Investors need to route their investments through ETFs or mutual funds that invest in these securities. Complex – These CMBS loans have a complex structure compared with traditional loans since they need to be collateralized before they are securitized. Further, since the underlying securities are commercial properties, the valuation is relatively complicated compared with residential properties. Because of the complexity of the products, these instruments require multiple participants in the value chain, which includes various types of servicers, trustees, custodians and credit rating agencies, to finally be put up these instruments in the market for investment purposes. Summing up CMBS are an excellent conduit to convert commercial lending into collateralized fixed income securities for investment purposes. However, unlike traditional mortgage-backed securities (MBS), CMBS loans are mortgaged on commercial properties and thus are more complex than the former. Investors should look at the underlying tranche of CMBS, such as investment grade, non-investment grade and unrated bonds, to identify the risk of credit and loss before making any investment decision basis their risk-return profile. Individual investors can route their investments in these instruments through mutual funds or ETFs that invest in such securities. In contrast, ultra-high net worth individuals and family offices can afford to invest directly in these instruments.