{"id":43728,"date":"2013-05-29T11:21:11","date_gmt":"2013-05-29T17:21:11","guid":{"rendered":"https:\/\/www.biggerpockets.com\/renewsblog\/?p=43728"},"modified":"2021-03-16T10:07:13","modified_gmt":"2021-03-16T16:07:13","slug":"2013-05-29-calculating-present-value-excel","status":"publish","type":"post","link":"https:\/\/www.biggerpockets.com\/blog\/2013-05-29-calculating-present-value-excel","title":{"rendered":"Calculating Present Value with Ease Using Excel"},"content":{"rendered":"

What would you do if someone was willing to contribute $200,000 to help you invest in a multifamily<\/a>\u00a0property?\u00a0 Your first thought would be; what\u2019s the catch?\u00a0 They tell you, you\u00a0must wait seven years to receive the money or you may have the money immediately.\u00a0 You must decide on waiting seven years or\u00a0receive $200,000 now? \u00a0The answer\u00a0should be pretty straight-forward, you want the money now.\u00a0 You can invest that $200,000 now.\u00a0\u00a0Seven years in the future your investment strategy may have changed.\u00a0 The $200,000 may or may not be as\u00a0important.\u00a0You know what you can do with the\u00a0money now.\u00a0 You cannot predict, with certainty, what you will need seven years into the future.<\/p>\n

The previous paragraph introduces you to some real estate investing concepts; time value of money, present value and future value.\u00a0 Money that is\u00a0available to you today for investing is more valuable than money in the future.<\/p>\n

What would happen if you were forced to wait the seven years for the $200,000?\u00a0 Seven years from now, can you predict the economic climate, the depreciation rate or cash flow conditions for multifamily properties?\u00a0 No one can, but if you have a crystal ball that can accurately make those kinds of predictions, please let us all know!\u00a0 Since we cannot predict, seven years into the future, maybe we should just sit and wait for the years to pass by.\u00a0 Some people would take that approach, not real estate investors.<\/p>\n

Predicting the Future<\/h2>\n

Investors know that they cannot predict the future; investors will still make plans into the future.\u00a0 Investors look at the value of that $200,000 that they will receive in the future in today\u2019s present day dollars.\u00a0 This is where the idea\u00a0of \u201ctime value of money\u201d comes from.\u00a0\u00a0To purchase multifamily properties, the value of the\u00a0money is affected by when the money is available or the\u00a0\u201ctime\u201d.<\/p>\n

Why would an investor care about determining the present value of future investment dollars?\u00a0 When\u00a0investing in multifamily properties, the question to be answered is; will the\u00a0property cash flow? \u00a0Cash flow is\u00a0money in the future.\u00a0 The cash flow from the property seven years from now is not as valuable as\u00a0the cash flow you receive today.\u00a0 Today\u2019s cash\u00a0flow can be used to purchase more rental properties now.\u00a0\u00a0Today\u2019s cash flow can be used for capital improvements that can lead to raising rents.\u00a0 Raised rents could mean increased cash flow.<\/p>\n

An investor will ask; what is the present value of future cash\u00a0flows?\u00a0 This answer will help determine the value of the property in today\u2019s dollars.\u00a0\u00a0With answering this question, how would you know if the price of a property was justified?\u00a0 How would you know the right time to sell or exchange the property?\u00a0 In order to answer these questions, you need a way of converting future values into\u00a0today\u2019s value.<\/p>\n

The factor that will convert future cash flow dollars into\u00a0today\u2019s dollar values is a discount rate.\u00a0A discount rate will discount the future values back to present values.\u00a0 An investor would need to determine what discount rate to use.\u00a0\u00a0The best rate would be one that is comparable to the rate of return you\u00a0would expect from other investments with similar risk factors.\u00a0\u00a0If you did not invest in a rental property, what other investment could\u00a0you invest in?\u00a0 This other\u00a0investment would have a certain rate of return.\u00a0 That rate of return would be your\u00a0discount rate to use for future cash flows of the rental property.<\/p>\n

Determining Excel Present Value<\/h2>\n

To get the present value of future cash flows, you need a formula.\u00a0 The formula is:<\/p>\n

PV = FV\/(1 +\u00a0r)^n<\/p>\n

PV is the Present Value, FV is the Future Value, the rate\u00a0per period is r and the number of periods is n.\u00a0 That is an intimidating formula that Excel can handle with ease.\u00a0 Thank goodness,\u00a0Excel has a Present Value (PV) function where you can enter the numbers and the\u00a0value is automatically calculated.<\/p>\n

Using Excel, we will test the PV() function on a multifamily property covering 10 years of cash flow values.\u00a0\u00a0 At the end of each year Excel will calculate the Present Value based upon a Discount Rate of our choosing and sample cash flow values.\u00a0 The picture below shows a portion of a worksheet that will display the Present Value results.<\/p>\n

\"Present<\/a><\/p>\n

With a fixed set of cash flow values for 10 years, all we need to do is enter a Discount Rate.\u00a0 The Present Values for each year would be calculated along with the largest three PV values highlighted with a red background.\u00a0 That was a very straightforward example of using Excel to calculate Present Values.<\/p>\n

Present Values can be used in other ways.\u00a0 For\u00a0a simple example, assume that we want the Present Value of a single cash flow\u00a0that you would receive at the end of four years.\u00a0 Consider a multifamily property that could sell\u00a0for $1,250,000 at the end of four years.\u00a0\u00a0The desired rate of return of this property will be 12.0% per year.\u00a0 What would be the appropriate sales\u00a0price in today\u2019s dollars in order to have a 12.0% rate of return?<\/p>\n

\"Sales<\/a><\/p>\n

Future Value<\/h2>\n

The worksheet has an extra row, Future Value.\u00a0 In this example, the Future Value that we want for the property is $1,250,000 in the fourth year.\u00a0 To get the sales price in today\u2019s dollars, the Discount Rate has been changed to 12%.\u00a0\u00a0 Time to have Excel solve for the Present Value, in this case it is $794,397.60.\u00a0 With a worksheet like the one above, you could enter various Future Values and Excel would calculate the sales price each year based upon your entered Discount Rate.<\/p>\n

The Present Value formula in cell F5 is; =-PV(C5,F2,,F4).\u00a0 Cell C5 is the 12% Discount Rate, cell F2 is the End of Year period and cell F4 is the Future Value of $1,250,000.\u00a0 Also notice the PV function is preceded by a negative.\u00a0 Present Value is negative to denote that it is money that is going out versus Future Value that is money coming in.\u00a0 The previous example can be stated this way; you purchase the apartment for $794,398 and after four years you\u00a0sell it for $1,250,000 you will earn a rate of return of 12.0% in that year.<\/p>\n

It is to your advantage as a real estate\u00a0investor to understand and use the time value of money, Present Value\u00a0and other key financial returns.\u00a0\u00a0Having a solid understanding of Excel would allow you to go beyond these one function examples.\u00a0 Real estate investing can involve many layers of analyses based upon various investing strategies.\u00a0 Producing one or two quick calculations is only the beginning.<\/p>\n

Photo: aussiegall<\/a><\/font><\/p>\n","protected":false},"excerpt":{"rendered":"

What would you do if someone was willing to contribute $200,000 to help you invest in a multifamily\u00a0property?\u00a0 Your first thought would be; what\u2019s the catch?\u00a0 They tell you, you\u00a0must […]<\/p>\n","protected":false},"author":809,"featured_media":43810,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[4241],"tags":[],"class_list":["post-43728","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-real-estate-business-management"],"acf":[],"comment_count":0,"_links":{"self":[{"href":"https:\/\/www.biggerpockets.com\/blog\/wp-json\/wp\/v2\/posts\/43728","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.biggerpockets.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.biggerpockets.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.biggerpockets.com\/blog\/wp-json\/wp\/v2\/users\/809"}],"replies":[{"embeddable":true,"href":"https:\/\/www.biggerpockets.com\/blog\/wp-json\/wp\/v2\/comments?post=43728"}],"version-history":[{"count":0,"href":"https:\/\/www.biggerpockets.com\/blog\/wp-json\/wp\/v2\/posts\/43728\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.biggerpockets.com\/blog\/wp-json\/wp\/v2\/media\/43810"}],"wp:attachment":[{"href":"https:\/\/www.biggerpockets.com\/blog\/wp-json\/wp\/v2\/media?parent=43728"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.biggerpockets.com\/blog\/wp-json\/wp\/v2\/categories?post=43728"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.biggerpockets.com\/blog\/wp-json\/wp\/v2\/tags?post=43728"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}