{ "cells": [ { "cell_type": "markdown", "id": "c2b9d5de", "metadata": {}, "source": [ "# Discounted Cash Flow" ] }, { "cell_type": "markdown", "id": "1f99587c", "metadata": {}, "source": [ "We consider the purchase of new equipment.\n", "\n", "1. The life span of the equipment is 12 years.\n", "\n", "2. Each year, the new equipment will save $1052.\n", "\n", "3. We use a discount rate $r = d - i = 0.02$.\n", "\n", "How much may the equipment cost to justify the purchase?" ] }, { "cell_type": "markdown", "id": "29fe53e7", "metadata": {}, "source": [ "We use a dataframe to answer this question, assuming the equipment costs 10000." ] }, { "cell_type": "code", "execution_count": 1, "id": "b7d6adae", "metadata": {}, "outputs": [], "source": [ "using DataFrames" ] }, { "cell_type": "markdown", "id": "8884f618", "metadata": {}, "source": [ "We initialize a dataframe with the number of years, starting at zero, which represents now." ] }, { "cell_type": "code", "execution_count": 2, "id": "b752437c", "metadata": {}, "outputs": [ { "data": { "text/html": [ "
13×1 DataFrame
Rowyear
Int64
10
21
32
43
54
65
76
87
98
109
1110
1211
1312
" ], "text/latex": [ "\\begin{tabular}{r|c}\n", "\t& year\\\\\n", "\t\\hline\n", "\t& Int64\\\\\n", "\t\\hline\n", "\t1 & 0 \\\\\n", "\t2 & 1 \\\\\n", "\t3 & 2 \\\\\n", "\t4 & 3 \\\\\n", "\t5 & 4 \\\\\n", "\t6 & 5 \\\\\n", "\t7 & 6 \\\\\n", "\t8 & 7 \\\\\n", "\t9 & 8 \\\\\n", "\t10 & 9 \\\\\n", "\t11 & 10 \\\\\n", "\t12 & 11 \\\\\n", "\t13 & 12 \\\\\n", "\\end{tabular}\n" ], "text/plain": [ "\u001b[1m13×1 DataFrame\u001b[0m\n", "\u001b[1m Row \u001b[0m│\u001b[1m year \u001b[0m\n", "\u001b[1m \u001b[0m│\u001b[90m Int64 \u001b[0m\n", "─────┼───────\n", " 1 │ 0\n", " 2 │ 1\n", " 3 │ 2\n", " 4 │ 3\n", " 5 │ 4\n", " 6 │ 5\n", " 7 │ 6\n", " 8 │ 7\n", " 9 │ 8\n", " 10 │ 9\n", " 11 │ 10\n", " 12 │ 11\n", " 13 │ 12" ] }, "execution_count": 2, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df = DataFrame(year = [k for k in 0:12])" ] }, { "cell_type": "markdown", "id": "cbbc9405", "metadata": {}, "source": [ "The second column in the dataframe will be the column with the annual savings." ] }, { "cell_type": "code", "execution_count": 3, "id": "f1950170", "metadata": {}, "outputs": [ { "data": { "text/plain": [ "13-element Vector{Int64}:\n", " 0\n", " 1052\n", " 1052\n", " 1052\n", " 1052\n", " 1052\n", " 1052\n", " 1052\n", " 1052\n", " 1052\n", " 1052\n", " 1052\n", " 1052" ] }, "execution_count": 3, "metadata": {}, "output_type": "execute_result" } ], "source": [ "annualsavings = [1052 for k=1:12]\n", "savingscolumn = [0; annualsavings]" ] }, { "cell_type": "markdown", "id": "1a5eff1c", "metadata": {}, "source": [ "We add the column with savings to the dataframe." ] }, { "cell_type": "code", "execution_count": 4, "id": "bd2dc9fc", "metadata": {}, "outputs": [ { "data": { "text/html": [ "
13×2 DataFrame
Rowyearsavings
Int64Int64
100
211052
321052
431052
541052
651052
761052
871052
981052
1091052
11101052
12111052
13121052
" ], "text/latex": [ "\\begin{tabular}{r|cc}\n", "\t& year & savings\\\\\n", "\t\\hline\n", "\t& Int64 & Int64\\\\\n", "\t\\hline\n", "\t1 & 0 & 0 \\\\\n", "\t2 & 1 & 1052 \\\\\n", "\t3 & 2 & 1052 \\\\\n", "\t4 & 3 & 1052 \\\\\n", "\t5 & 4 & 1052 \\\\\n", "\t6 & 5 & 1052 \\\\\n", "\t7 & 6 & 1052 \\\\\n", "\t8 & 7 & 1052 \\\\\n", "\t9 & 8 & 1052 \\\\\n", "\t10 & 9 & 1052 \\\\\n", "\t11 & 10 & 1052 \\\\\n", "\t12 & 11 & 1052 \\\\\n", "\t13 & 12 & 1052 \\\\\n", "\\end{tabular}\n" ], "text/plain": [ "\u001b[1m13×2 DataFrame\u001b[0m\n", "\u001b[1m Row \u001b[0m│\u001b[1m year \u001b[0m\u001b[1m savings \u001b[0m\n", "\u001b[1m \u001b[0m│\u001b[90m Int64 \u001b[0m\u001b[90m Int64 \u001b[0m\n", "─────┼────────────────\n", " 1 │ 0 0\n", " 2 │ 1 1052\n", " 3 │ 2 1052\n", " 4 │ 3 1052\n", " 5 │ 4 1052\n", " 6 │ 5 1052\n", " 7 │ 6 1052\n", " 8 │ 7 1052\n", " 9 │ 8 1052\n", " 10 │ 9 1052\n", " 11 │ 10 1052\n", " 12 │ 11 1052\n", " 13 │ 12 1052" ] }, "execution_count": 4, "metadata": {}, "output_type": "execute_result" } ], "source": [ "savings = \"savings\"\n", "df[!,:savings] = savingscolumn\n", "df" ] }, { "cell_type": "markdown", "id": "8a557f9b", "metadata": {}, "source": [ "Assume the equipment costs $10,000." ] }, { "cell_type": "code", "execution_count": 5, "id": "89a2d2de", "metadata": {}, "outputs": [ { "data": { "text/plain": [ "13-element Vector{Int64}:\n", " -10000\n", " 0\n", " 0\n", " 0\n", " 0\n", " 0\n", " 0\n", " 0\n", " 0\n", " 0\n", " 0\n", " 0\n", " 0" ] }, "execution_count": 5, "metadata": {}, "output_type": "execute_result" } ], "source": [ "annualcosts = [0 for k=1:12]\n", "costscolumn = [-10000; annualcosts]" ] }, { "cell_type": "markdown", "id": "71acf9a3", "metadata": {}, "source": [ "The third column in the dataframe represents the annual cost." ] }, { "cell_type": "code", "execution_count": 6, "id": "c24f5b8f", "metadata": {}, "outputs": [ { "data": { "text/html": [ "
13×3 DataFrame
Rowyearsavingscosts
Int64Int64Int64
100-10000
2110520
3210520
4310520
5410520
6510520
7610520
8710520
9810520
10910520
111010520
121110520
131210520
" ], "text/latex": [ "\\begin{tabular}{r|ccc}\n", "\t& year & savings & costs\\\\\n", "\t\\hline\n", "\t& Int64 & Int64 & Int64\\\\\n", "\t\\hline\n", "\t1 & 0 & 0 & -10000 \\\\\n", "\t2 & 1 & 1052 & 0 \\\\\n", "\t3 & 2 & 1052 & 0 \\\\\n", "\t4 & 3 & 1052 & 0 \\\\\n", "\t5 & 4 & 1052 & 0 \\\\\n", "\t6 & 5 & 1052 & 0 \\\\\n", "\t7 & 6 & 1052 & 0 \\\\\n", "\t8 & 7 & 1052 & 0 \\\\\n", "\t9 & 8 & 1052 & 0 \\\\\n", "\t10 & 9 & 1052 & 0 \\\\\n", "\t11 & 10 & 1052 & 0 \\\\\n", "\t12 & 11 & 1052 & 0 \\\\\n", "\t13 & 12 & 1052 & 0 \\\\\n", "\\end{tabular}\n" ], "text/plain": [ "\u001b[1m13×3 DataFrame\u001b[0m\n", "\u001b[1m Row \u001b[0m│\u001b[1m year \u001b[0m\u001b[1m savings \u001b[0m\u001b[1m costs \u001b[0m\n", "\u001b[1m \u001b[0m│\u001b[90m Int64 \u001b[0m\u001b[90m Int64 \u001b[0m\u001b[90m Int64 \u001b[0m\n", "─────┼────────────────────────\n", " 1 │ 0 0 -10000\n", " 2 │ 1 1052 0\n", " 3 │ 2 1052 0\n", " 4 │ 3 1052 0\n", " 5 │ 4 1052 0\n", " 6 │ 5 1052 0\n", " 7 │ 6 1052 0\n", " 8 │ 7 1052 0\n", " 9 │ 8 1052 0\n", " 10 │ 9 1052 0\n", " 11 │ 10 1052 0\n", " 12 │ 11 1052 0\n", " 13 │ 12 1052 0" ] }, "execution_count": 6, "metadata": {}, "output_type": "execute_result" } ], "source": [ "costs = \"costs\"\n", "df[!,:costs] = costscolumn\n", "df" ] }, { "cell_type": "markdown", "id": "bd9cffac", "metadata": {}, "source": [ "Now we compute the present values, using the discount rate of 0.02." ] }, { "cell_type": "code", "execution_count": 7, "id": "19967b0c", "metadata": {}, "outputs": [ { "data": { "text/plain": [ "13-element Vector{Float64}:\n", " 0.0\n", " 1031.1690043187066\n", " 1010.750489988244\n", " 990.7362893306297\n", " 971.1183963987409\n", " 951.8889637738295\n", " 933.0402994264497\n", " 914.5648636395438\n", " 896.4552659924543\n", " 878.7042624046582\n", " 861.3047522380368\n", " 844.2497754565273\n", " 827.5325098420143" ] }, "execution_count": 7, "metadata": {}, "output_type": "execute_result" } ], "source": [ "r = 0.02\n", "s = df[!,savings]\n", "presentvalues = [s[k+1]*exp(-k*r) for k=0:12]" ] }, { "cell_type": "code", "execution_count": 8, "id": "e1b7fc3c", "metadata": {}, "outputs": [ { "data": { "text/html": [ "
13×4 DataFrame
Rowyearsavingscostsvalues
Int64Int64Int64Float64
100-100000.0
21105201031.17
32105201010.75
4310520990.736
5410520971.118
6510520951.889
7610520933.04
8710520914.565
9810520896.455
10910520878.704
111010520861.305
121110520844.25
131210520827.533
" ], "text/latex": [ "\\begin{tabular}{r|cccc}\n", "\t& year & savings & costs & values\\\\\n", "\t\\hline\n", "\t& Int64 & Int64 & Int64 & Float64\\\\\n", "\t\\hline\n", "\t1 & 0 & 0 & -10000 & 0.0 \\\\\n", "\t2 & 1 & 1052 & 0 & 1031.17 \\\\\n", "\t3 & 2 & 1052 & 0 & 1010.75 \\\\\n", "\t4 & 3 & 1052 & 0 & 990.736 \\\\\n", "\t5 & 4 & 1052 & 0 & 971.118 \\\\\n", "\t6 & 5 & 1052 & 0 & 951.889 \\\\\n", "\t7 & 6 & 1052 & 0 & 933.04 \\\\\n", "\t8 & 7 & 1052 & 0 & 914.565 \\\\\n", "\t9 & 8 & 1052 & 0 & 896.455 \\\\\n", "\t10 & 9 & 1052 & 0 & 878.704 \\\\\n", "\t11 & 10 & 1052 & 0 & 861.305 \\\\\n", "\t12 & 11 & 1052 & 0 & 844.25 \\\\\n", "\t13 & 12 & 1052 & 0 & 827.533 \\\\\n", "\\end{tabular}\n" ], "text/plain": [ "\u001b[1m13×4 DataFrame\u001b[0m\n", "\u001b[1m Row \u001b[0m│\u001b[1m year \u001b[0m\u001b[1m savings \u001b[0m\u001b[1m costs \u001b[0m\u001b[1m values \u001b[0m\n", "\u001b[1m \u001b[0m│\u001b[90m Int64 \u001b[0m\u001b[90m Int64 \u001b[0m\u001b[90m Int64 \u001b[0m\u001b[90m Float64 \u001b[0m\n", "─────┼──────────────────────────────────\n", " 1 │ 0 0 -10000 0.0\n", " 2 │ 1 1052 0 1031.17\n", " 3 │ 2 1052 0 1010.75\n", " 4 │ 3 1052 0 990.736\n", " 5 │ 4 1052 0 971.118\n", " 6 │ 5 1052 0 951.889\n", " 7 │ 6 1052 0 933.04\n", " 8 │ 7 1052 0 914.565\n", " 9 │ 8 1052 0 896.455\n", " 10 │ 9 1052 0 878.704\n", " 11 │ 10 1052 0 861.305\n", " 12 │ 11 1052 0 844.25\n", " 13 │ 12 1052 0 827.533" ] }, "execution_count": 8, "metadata": {}, "output_type": "execute_result" } ], "source": [ "values = \"values\"\n", "df[!,:values] = presentvalues\n", "df" ] }, { "cell_type": "markdown", "id": "3748e764", "metadata": {}, "source": [ "Now we add the row with the sums of the savings, the costs, and the present values." ] }, { "cell_type": "code", "execution_count": 9, "id": "f912e02d", "metadata": {}, "outputs": [ { "data": { "text/html": [ "
14×4 DataFrame
Rowyearsavingscostsvalues
Int64Int64Int64Float64
100-100000.0
21105201031.17
32105201010.75
4310520990.736
5410520971.118
6510520951.889
7610520933.04
8710520914.565
9810520896.455
10910520878.704
111010520861.305
121110520844.25
131210520827.533
14012624-1000011111.5
" ], "text/latex": [ "\\begin{tabular}{r|cccc}\n", "\t& year & savings & costs & values\\\\\n", "\t\\hline\n", "\t& Int64 & Int64 & Int64 & Float64\\\\\n", "\t\\hline\n", "\t1 & 0 & 0 & -10000 & 0.0 \\\\\n", "\t2 & 1 & 1052 & 0 & 1031.17 \\\\\n", "\t3 & 2 & 1052 & 0 & 1010.75 \\\\\n", "\t4 & 3 & 1052 & 0 & 990.736 \\\\\n", "\t5 & 4 & 1052 & 0 & 971.118 \\\\\n", "\t6 & 5 & 1052 & 0 & 951.889 \\\\\n", "\t7 & 6 & 1052 & 0 & 933.04 \\\\\n", "\t8 & 7 & 1052 & 0 & 914.565 \\\\\n", "\t9 & 8 & 1052 & 0 & 896.455 \\\\\n", "\t10 & 9 & 1052 & 0 & 878.704 \\\\\n", "\t11 & 10 & 1052 & 0 & 861.305 \\\\\n", "\t12 & 11 & 1052 & 0 & 844.25 \\\\\n", "\t13 & 12 & 1052 & 0 & 827.533 \\\\\n", "\t14 & 0 & 12624 & -10000 & 11111.5 \\\\\n", "\\end{tabular}\n" ], "text/plain": [ "\u001b[1m14×4 DataFrame\u001b[0m\n", "\u001b[1m Row \u001b[0m│\u001b[1m year \u001b[0m\u001b[1m savings \u001b[0m\u001b[1m costs \u001b[0m\u001b[1m values \u001b[0m\n", "\u001b[1m \u001b[0m│\u001b[90m Int64 \u001b[0m\u001b[90m Int64 \u001b[0m\u001b[90m Int64 \u001b[0m\u001b[90m Float64 \u001b[0m\n", "─────┼───────────────────────────────────\n", " 1 │ 0 0 -10000 0.0\n", " 2 │ 1 1052 0 1031.17\n", " 3 │ 2 1052 0 1010.75\n", " 4 │ 3 1052 0 990.736\n", " 5 │ 4 1052 0 971.118\n", " 6 │ 5 1052 0 951.889\n", " 7 │ 6 1052 0 933.04\n", " 8 │ 7 1052 0 914.565\n", " 9 │ 8 1052 0 896.455\n", " 10 │ 9 1052 0 878.704\n", " 11 │ 10 1052 0 861.305\n", " 12 │ 11 1052 0 844.25\n", " 13 │ 12 1052 0 827.533\n", " 14 │ 0 12624 -10000 11111.5" ] }, "execution_count": 9, "metadata": {}, "output_type": "execute_result" } ], "source": [ "push!(df,[0,sum(df[!,savings]),sum(df[!,costs]), sum(df[!,values])])" ] }, { "cell_type": "markdown", "id": "3774b156", "metadata": {}, "source": [ "Adding up the present values shows that $11111.5 is the answer to the original question for the justification of the purchase." ] }, { "cell_type": "markdown", "id": "903e2b55", "metadata": {}, "source": [ "In which year do we recover the cost of the investment?" ] }, { "cell_type": "code", "execution_count": 10, "id": "326a4dc3", "metadata": {}, "outputs": [ { "data": { "text/plain": [ "14-element Vector{Float64}:\n", " -10000.0\n", " -8968.830995681294\n", " -7958.080505693049\n", " -6967.3442163624195\n", " -5996.225819963678\n", " -5044.336856189849\n", " -4111.296556763399\n", " -3196.7316931238556\n", " -2300.276427131401\n", " -1421.572164726742\n", " -560.2674124887053\n", " 283.98236296782125\n", " 1111.5148728098357\n", " 0.0" ] }, "execution_count": 10, "metadata": {}, "output_type": "execute_result" } ], "source": [ "subsums = [df[1,3] + sum(df[i,4] for i=1:k) for k=1:13]\n", "lastcolumn = [subsums; 0]" ] }, { "cell_type": "code", "execution_count": 11, "id": "2c1c6e0e", "metadata": {}, "outputs": [ { "data": { "text/html": [ "
14×5 DataFrame
Rowyearsavingscostsvaluesbalance
Int64Int64Int64Float64Float64
100-100000.0-10000.0
21105201031.17-8968.83
32105201010.75-7958.08
4310520990.736-6967.34
5410520971.118-5996.23
6510520951.889-5044.34
7610520933.04-4111.3
8710520914.565-3196.73
9810520896.455-2300.28
10910520878.704-1421.57
111010520861.305-560.267
121110520844.25283.982
131210520827.5331111.51
14012624-1000011111.50.0
" ], "text/latex": [ "\\begin{tabular}{r|ccccc}\n", "\t& year & savings & costs & values & balance\\\\\n", "\t\\hline\n", "\t& Int64 & Int64 & Int64 & Float64 & Float64\\\\\n", "\t\\hline\n", "\t1 & 0 & 0 & -10000 & 0.0 & -10000.0 \\\\\n", "\t2 & 1 & 1052 & 0 & 1031.17 & -8968.83 \\\\\n", "\t3 & 2 & 1052 & 0 & 1010.75 & -7958.08 \\\\\n", "\t4 & 3 & 1052 & 0 & 990.736 & -6967.34 \\\\\n", "\t5 & 4 & 1052 & 0 & 971.118 & -5996.23 \\\\\n", "\t6 & 5 & 1052 & 0 & 951.889 & -5044.34 \\\\\n", "\t7 & 6 & 1052 & 0 & 933.04 & -4111.3 \\\\\n", "\t8 & 7 & 1052 & 0 & 914.565 & -3196.73 \\\\\n", "\t9 & 8 & 1052 & 0 & 896.455 & -2300.28 \\\\\n", "\t10 & 9 & 1052 & 0 & 878.704 & -1421.57 \\\\\n", "\t11 & 10 & 1052 & 0 & 861.305 & -560.267 \\\\\n", "\t12 & 11 & 1052 & 0 & 844.25 & 283.982 \\\\\n", "\t13 & 12 & 1052 & 0 & 827.533 & 1111.51 \\\\\n", "\t14 & 0 & 12624 & -10000 & 11111.5 & 0.0 \\\\\n", "\\end{tabular}\n" ], "text/plain": [ "\u001b[1m14×5 DataFrame\u001b[0m\n", "\u001b[1m Row \u001b[0m│\u001b[1m year \u001b[0m\u001b[1m savings \u001b[0m\u001b[1m costs \u001b[0m\u001b[1m values \u001b[0m\u001b[1m balance \u001b[0m\n", "\u001b[1m \u001b[0m│\u001b[90m Int64 \u001b[0m\u001b[90m Int64 \u001b[0m\u001b[90m Int64 \u001b[0m\u001b[90m Float64 \u001b[0m\u001b[90m Float64 \u001b[0m\n", "─────┼───────────────────────────────────────────────\n", " 1 │ 0 0 -10000 0.0 -10000.0\n", " 2 │ 1 1052 0 1031.17 -8968.83\n", " 3 │ 2 1052 0 1010.75 -7958.08\n", " 4 │ 3 1052 0 990.736 -6967.34\n", " 5 │ 4 1052 0 971.118 -5996.23\n", " 6 │ 5 1052 0 951.889 -5044.34\n", " 7 │ 6 1052 0 933.04 -4111.3\n", " 8 │ 7 1052 0 914.565 -3196.73\n", " 9 │ 8 1052 0 896.455 -2300.28\n", " 10 │ 9 1052 0 878.704 -1421.57\n", " 11 │ 10 1052 0 861.305 -560.267\n", " 12 │ 11 1052 0 844.25 283.982\n", " 13 │ 12 1052 0 827.533 1111.51\n", " 14 │ 0 12624 -10000 11111.5 0.0" ] }, "execution_count": 11, "metadata": {}, "output_type": "execute_result" } ], "source": [ "balance = \"balance\"\n", "df[!,:balance] = lastcolumn\n", "df" ] }, { "cell_type": "markdown", "id": "6c94ec0a", "metadata": {}, "source": [ "We see that in the eleventh year, we have recovered the cost of the investment, under the assumption that its purchase costed 10000." ] } ], "metadata": { "kernelspec": { "display_name": "Julia 1.12", "language": "julia", "name": "julia-1.12" }, "language_info": { "file_extension": ".jl", "mimetype": "application/julia", "name": "julia", "version": "1.12.4" } }, "nbformat": 4, "nbformat_minor": 5 }