저자: 미상 · 외국인
요약 보기
피터 린치의 배당 조정 적정가치(Peter Lynch Fair Value) 공식을 구글 시트로 계산하는 방법을 설명한다. 공식은 (예상 이익성장률 + 배당수익률) / PER이며, 1 미만이면 고평가, 1이면 적정, 2 이상이면 저평가로 본다. 예시로 인텔(INTC)을 계산해 0.39(고평가)가 나왔고, 이를 역산해 내재가치와 20% 안전마진 적용 적정주가를 구한다. 가격 예측이 아닌 밸류에이션 도구 설명이다.
다듬은 글
hello and welcome to this Google sheet tutorial today we're diving into the wisdom of a legendary figure in the world of investing Peter Lynch Lynch a renowned fund manager and author left an indelible Mark with his successful tenure at Fidelity mellan Fund one of his invaluable contributions is the concept of dividend adjusted fair value Peter Lynch born in 1944 earned a claim for his remarkable insights and straightforward approach to investing serving as the manager of fidelity mellin from 197 7 to 1990 Lynch consistently outperformed the market making him a beacon for investors seeking astute guidance Lynch's dividend adjusted fair value formula takes a unique perspective on stock valuation today we'll unravel the intricacies of Lynch's approach understanding how he factors in dividends to determine a Stock's fair value so buckle up as we navigate through the principles and practical application of Peter Lynch's dividend adjusted fair value exploring how you can implement this methodology using Google Sheets let's jump over to Google Sheets and see how this can be done in Peter Lynch's dividend adjusted fair value we find the intrinsic value of a stock by calculating the sum of the earnings growth rate and dividend yield divided by the price to earnings ratio the result is the Peter Lynch fair value where a value below one means that the stock is overvalued if the result is equal one then the stock has a fair value a result of for example two indicates that the stock is undervalued and if the result is above three then the stock is true trading at a significant discount now let's see how we can calculate this value using Google Sheets we start by writing ticker and then add a stock ticker for this example we will use Intel then we need the current stock price the future growth estimate the price to earnings and the dividend yield so we write all of these below we then import the current stock price by using the Google Finance function to do this we write equals Google Finance and designate the stock ticker sell this gives us the current stock price for Intel next we need to find the future growth estimate this data can be found on Yahoo finance so let's go to that page and see if we can localize this here we have the Yahoo finance page for Intel to find the growth estimates we go to the analysis tab all the way to the bottom is the growth estimates per year for the next 5 years we will use this estimate for the projected company growth let's go back to the spreadsheet here we write 39.5 n in the cell for the future growth estimate the next step is to determine the company's price to earnings ratio again we can use the Google Finance function for this F14 calls in the ticker for Intel and by writing PE in quotation marks we get the price to earnings for our company of Interest the next is to get the dividend yield to make this a bit more easy to follow we will first import the data from finviz.com and then clean it afterwards this will end up giving us the percent dividend yield we go to finviz.com and write Inc in the search field this will bring up financial data from Intel down in this table which is called table 10 we find the trailing 12 months dividend in column 2 Row 8 we will need this in a moment when we call in the data from this table to our Google sheet first in order to import this data we need to copy part of the URL and go back to our spreadsheet we need to import this data but we will do it further down in the spreadsheet where it will be out of sight to import this dat data we write index import HTML and remember here to copy in the URL between two quotation marks to link this to the ticker symbol we will write and and F14 then we designate table 10 column 2 and line 8 this will import the dividend data now we just need to clean the data by removing the stars and the dividend amount so we end up with only the dividend percent to do this we will use the substitute mid and find functions to remove everything but the percent number I will start by writing in the formula and then explain the different parts in order to not make this too lengthy and boring I will fast forward and here we have it let's take a look at the different parts of the formula the part with find F31 minus find F31 minus1 finds the position of the opening and closing parenthesis and calculates the length of the text between them the mid function extracts the text between the opening and closing parentheses from cell D31 The Substitute function replaces instances of percent with something else in this case nothing the multiply by one multiplies the text representation of the number by one effectively converting it to a numerical value this gives us the 1.19 for the dividend yield now we can find the Peter Lynch dividend adjusted fair value we do this by taking the growth rate and add the dividend yield then we divide by the price to earnings and here we have a Peter Lynch dividend Ed fair value for Intel the value is 0.39 which is below one this indicates that the stock is overvalued as stated in the title of the video we want to calculate the intrinsic value using Peter Lynch dividend adjusted fair value by using a bit of math we can convert this number into an intrinsic value for Intel first we assume that the stock is fairly valued and thereby set the Peter Lynch fair value to one we can rearrange the equation so it is the price to earnings that equals earnings growth rate plus the dividend yield then we use the fact that the price to earnings are equal to the stock price divided by the earnings per share we can then insert this in the formula instead of the price to earnings this gives us stock price divided by the earnings per share equals earnings growth rate plus the dividend yield by multiplying earnings per share on both sides of the equation we get stock price equals earnings growth rate plus the dividend yield multiplied by earnings per share and here the stock price is the intrinsic valuation of the stock as we assume that if Fair valued stock had a Peter Lynch fair value of one going back to our spreadsheet in order to calculate the intrinsic value we need the earnings per share this can be imported by again using the Google Finance function and designating the ticker and then writing EPs and quotation marks we can now calculate the intrinsic value for the stock we do so by adding the growth estimate and the dividend yield and then multiplying the result by the earnings per share now we only need to add a margin of safety to make this into percentage we need to format the data by choosing format numbers and then percentage let's add 20% as the margin of safety for Intel we can now calculate the final acceptable stock price for Intel by taking the intrinsic value and then subtracting the margin of safety F22 minus F22 multiplied by f23 if we want to color code the intrinsic value so it is red if it is under the stock price and green if it is above we go to format and then conditional formatting then we add less than and choose sell F-15 for the current stock price we then choose red as the color we repeat this and now choose greater than and green for the color this will color code the intrinsic value so it reflects the valuation compared to the current stock price now we only need to make it all a bit more pretty so let's do that and there you have at a spreadsheet for an easy calculation of the the intrinsic value using Peter Lynch's dividend adjusted fair value if we want to find the Peter Lynch's dividend adjusted fair value for a new company we just need to change the ticker and the spreadsheet will automatically import the required data the only thing we need to find is the projected growth which can be found on for example Yahoo finance I have inserted the ticker for vone and added its projected growth and as you can see the spreadsheet has now updated the Peter Lynch fair value and the acceptable stock price as the acceptable stock price is higher than the current stock price we get a green coloring indicating that this stock is trading at a discount remember this is a tool in your financial toolkit and combining it with thorough research and Analysis is key Peter Lynch dividend adjusted fair value is one of five parts of our valuation tool which is available on our patreon page for your convenience you can find the link to the page in the video description below if you found this tutorial valuable give it a thumbs up share it with your fellow investors and don't forget to subscribe for more financial insight s
Comments 0