Future value in Power BI
1. Future value in Power BI
Hello again! In this video, we will review future value. Future value finds what money today will be worth in the future. The function essentially works as a growth rate, as money grows at the desired percentage each period. Therefore, we could use it to create a straight-line forecast as well. Let’s say we want to use future value to forecast gross sales five years from now at a 5% growth rate. We should take the last year of gross sales and then plug it into our FV() function. We’ll call this “Gross Sales 2026E”, for estimate. The future value syntax includes rate, which is the interest rate, number of periods, payments, which are used for annuities, and type, which is used to determine if annuity payments are made at the beginning or end of the period. This can impact when the compounding happens. We’ll set our rate to match the growth rate of 5%. We’ll put five years, and there are no payments, so we’ll put zero, and finally, we’ll use Gross Sales 2021 as the present value. Now we’ll add this to the table and compare it to the original number. Oops! We’ll notice the number is negative. This is because future value is assuming an initial investment amount, which requires an outlay of cash. So we’ll go back to our measure and make PV negative. Now let’s look at an example with an annuity. Say that Spaero just won a lawsuit and will be paid $100,000 monthly for the next 20 months. If Spaero's reinvestment rate is 6% annually, how much will the payouts be worth at the end of 20 months? Just like in the example before, we can use the future value function for our new measure. Notice in the problem that the rate is annual but Spaero receives monthly payments. This means we need to convert the annual rate of 6% to a monthly rate, which can be done by dividing by 12. Next, since the periods are already in months we can just use 20, and the payment here is $100,000. Remember to make it negative. Since there is no present value, we can leave PV blank, and because the problem didn’t specify that payments were made at the beginning of the month, we can just assume they are made at the end by leaving type blank as well. Great, now we can run the measure and add it to the card, and we’ll see that the payout will be worth 2 point 1 million dollars at the end of 20 months. Finally, let’s look at an example with the payment function. The syntax for PMT is very similar to the future value function, only that payment and future value have traded places. Let’s say we want to become millionaires by investing in stocks at the beginning of each month over the next ten years. Historically, stocks have returned 10% annually. And since we have an annual rate of 10%, we’ll need to divide that by 12 to make it monthly. And same with years. We have a 10-year investment horizon, but the payments are made monthly, so we need to multiply our years by the 12 compounding periods. We’re assuming that we don’t have any savings currently, and we want to have one million dollars by the end of our analysis, so for future value, we’ll plug in one million dollars. Also, since payments are made at the beginning of each month, here we’ll want to put 1 for type. Now we’ll run the measure and add it to our card. Let’s edit the card so we can get the exact dollar amount. Go to formatting, callout value, and display units, and none. Now we can see we need to invest $4,841.40 each month to become millionaires by the end of 10 years! We did some pretty interesting analysis with future value, and now that you’ve seen it, it’s your turn to try.2. Let's practice!
Create Your Free Account
or
By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.