Last active
August 29, 2015 14:04
-
-
Save hinrik/9cf03ec1dc3455eada9b to your computer and use it in GitHub Desktop.
Dutch mortgage calculator for 2014
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| use 5.14.0; | |
| use warnings; | |
| use Getopt::Long qw(:config no_ignore_case bundling); | |
| use List::Util 'sum'; | |
| use Spreadsheet::Engine::Function::PMT; | |
| # up to date as of 2014 | |
| my $max_deduction = 51.5; | |
| my $min_deduction = 42; | |
| my $yearly_reduction = 5; | |
| my $tax_credit = 2103; | |
| my $invest_return = 5; | |
| my @tax_brackets = ( | |
| [19_645, 5.1], | |
| [13_718, 10.85], | |
| [23_168, 42], | |
| [999999, 52], | |
| ); | |
| my %args; | |
| my $success = GetOptions( | |
| 'p|principal=s' => \my $principal, | |
| 's|salary=s' => \my $salary, | |
| 't|term=s@' => \my @terms, | |
| 'b|bump=s' => \(my $bump = 0), | |
| 'r|ruling=s' => \(my $ruling_years = 0), | |
| ); | |
| if (!$success || !$principal || !$salary || !@terms) { | |
| die "Usage: perl mort.pl --principal=250000 --salary=50000 --bump=3 --ruling=5 --term 10:3.5 --term 20:4.5\n"; | |
| } | |
| @terms = map { [split /:/, $_] } @terms; | |
| my $years = sum(map { $_->[0] } @terms); | |
| my $annuity = calculate_annuity($principal, $years, \@terms); | |
| my $linear = calculate_linear($principal, $years, \@terms); | |
| add_aggregates_and_net($_, $salary, $bump, $tax_credit, $max_deduction, $yearly_reduction, $ruling_years) for $annuity, $linear; | |
| print "\n"; | |
| printf "Mortgage duration: %i years\n", $years; | |
| printf "Principal amount: %i\n", $principal; | |
| printf "Gross yearly salary: %i\n", $salary; | |
| printf "Yearly salary bump: %i%%\n", $bump || 0; | |
| printf "30%% ruling for: %i years\n", $ruling_years; | |
| print "\nInterest terms:\n"; | |
| printf " %2i years @ %.2f%%\n", @$_ for @terms; | |
| print "\n"; | |
| compare($years, $annuity, $linear); | |
| for my $m (['Annuity', $annuity], ['Linear', $linear]) { | |
| my ($type, $mortgage) = @$m; | |
| print "$type\n------------------\n"; | |
| printf("Gross cost: %6i\n", $mortgage->{gross}); | |
| printf("Deduction: %6i\n", $mortgage->{gross} - $mortgage->{net}); | |
| printf("Net cost: %6i\n", $mortgage->{net}); | |
| for my $year_no (1..@{ $mortgage->{years} }) { | |
| my $year = $mortgage->{years}[$year_no-1]; | |
| printf "\nYear %2i (%.2f%%)", $year_no, $year->{rate}; | |
| printf " Repayment: %5i", $year->{repayment}; | |
| printf " Interest: %5i", $year->{interest}; | |
| printf " Gross: %5i", $year->{gross}; | |
| printf " Deduction: %5i", $year->{gross} - $year->{net}; | |
| printf " Net: %5i", $year->{net}; | |
| printf " Avg monthly gross: %4i", $year->{gross} / 12; | |
| printf " Avg monthly net: %4i", $year->{net} / 12; | |
| } | |
| print "\n\n\n"; | |
| } | |
| # fixed monthly gross (principal+interest) payments | |
| sub calculate_annuity { | |
| my ($principal, $years, $terms) = @_; | |
| my %mortgage; | |
| for my $term (@$terms) { | |
| my ($term_years, $yearly_rate) = @$term; | |
| my $monthly_gross = -Spreadsheet::Engine::Function::PMT->calculate( | |
| (1+$yearly_rate/100)**(1/12)-1, $years * 12, $principal, | |
| ); | |
| for (1..$term_years) { | |
| my %year = (rate => $yearly_rate); | |
| for my $month (1..12) { | |
| my $monthly_interest = ($principal * $yearly_rate/100) / 12; | |
| my $monthly_repayment = $monthly_gross - $monthly_interest; | |
| $principal -= $monthly_repayment; | |
| push @{ $year{months} }, { | |
| gross => $monthly_gross, | |
| interest => $monthly_interest, | |
| repayment => $monthly_repayment, | |
| }; | |
| } | |
| push @{ $mortgage{years} }, \%year; | |
| } | |
| $years -= $term_years; | |
| } | |
| return \%mortgage; | |
| } | |
| # fixed monthly principal payments | |
| sub calculate_linear { | |
| my ($principal, $years, $terms) = @_; | |
| my %mortgage; | |
| my $monthly_repayment = $principal / $years / 12; | |
| for my $term (@$terms) { | |
| my ($term_years, $yearly_rate) = @$term; | |
| for (1..$term_years) { | |
| my %year = (rate => $yearly_rate); | |
| for (1..12) { | |
| my $monthly_interest = ($principal * $yearly_rate/100) / 12; | |
| my $monthly_gross = $monthly_repayment + $monthly_interest; | |
| $principal -= $monthly_repayment; | |
| push @{ $year{months} }, { | |
| gross => $monthly_gross, | |
| interest => $monthly_interest, | |
| repayment => $monthly_repayment, | |
| }; | |
| } | |
| push @{ $mortgage{years} }, \%year; | |
| } | |
| } | |
| return \%mortgage; | |
| } | |
| sub add_aggregates_and_net { | |
| my ($mortgage, $salary, $bump, $tax_credit, $max_deduction, $yearly_reduction, $ruling_years) = @_; | |
| for my $year (@{ $mortgage->{years} }) { | |
| my $taxable_salary = $ruling_years ? $salary * 0.7 : $salary; | |
| $taxable_salary -= $tax_credit; | |
| my $yearly_interest = sum(map { $_->{interest} } @{ $year->{months} }); | |
| my $yearly_refund = tax($taxable_salary, $max_deduction) - tax($taxable_salary-$yearly_interest, $max_deduction); | |
| my $yearly_gross = sum(map { $_->{gross} } @{ $year->{months} }); | |
| for my $month (@{ $year->{months} }) { | |
| my $monthly_refund = $month->{interest} / $yearly_interest * $yearly_refund; | |
| $month->{net} = $month->{gross} - $monthly_refund; | |
| } | |
| $year->{gross} = $yearly_gross; | |
| $year->{interest} = $yearly_interest; | |
| $year->{net} = $yearly_gross - $yearly_refund; | |
| $year->{repayment} = sum(map { $_->{repayment} } @{ $year->{months} }); | |
| $salary *= (1+$bump/100) if $bump; | |
| $ruling_years-- if $ruling_years; | |
| $max_deduction -= $yearly_reduction if $max_deduction > $min_deduction; | |
| } | |
| for my $amount (qw(interest net gross)) { | |
| $mortgage->{$amount} = sum(map { $_->{$amount} } @{ $mortgage->{years} } ); | |
| } | |
| return; | |
| } | |
| sub tax { | |
| my ($salary, $max_deduction) = @_; | |
| my $tax; | |
| for my $bracket (@tax_brackets) { | |
| my ($level, $rate) = @$bracket; | |
| my $taxable = $salary >= $level ? $level : $salary; | |
| $rate = $max_deduction if $max_deduction && $rate > $max_deduction; | |
| $tax += $taxable * ($rate/100); | |
| $salary -= $taxable; | |
| last if !$salary; | |
| } | |
| return $tax; | |
| } | |
| sub compare { | |
| my ($years, $annuity, $linear) = @_; | |
| say <<BLURB; | |
| If we take the difference in net yearly mortgage payments and calculate | |
| an ROI of 5%, we can see which type of mortgage is cheaper in the long | |
| run if you are investing your spare income in index funds with a 5% return. | |
| The annuity mortgage has a higher net payment total than a linear mortgage, | |
| but the extra investment returns can more than make up for it due to the | |
| annuity mortgage's higher ratio of (tax-deductible) interest to principal | |
| payments, especially in the early years. | |
| BLURB | |
| my $linear_invest = 0; | |
| my $annuity_invest = 0; | |
| for my $year (0..$years-1) { | |
| my $linear_net = $linear->{years}[$year]{net}; | |
| my $annuity_net = $annuity->{years}[$year]{net}; | |
| if ($linear_net > $annuity_net) { | |
| $annuity_invest += $linear_net - $annuity_net; | |
| } | |
| elsif ($annuity_net > $linear_net) { | |
| $linear_invest += $annuity_net - $linear_net; | |
| } | |
| $annuity_invest *= (1+$invest_return/100); | |
| $linear_invest *= (1+$invest_return/100); | |
| } | |
| printf " Annuity investment earnings: %6i\n", $annuity_invest; | |
| printf " Linear investment earnings: %6i\n", $linear_invest; | |
| print "\n"; | |
| } |
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| $ perl dutch_mortgage.pl -p 250000 -s 50000 -r 5 -b 3 -t 10:3.25 -t 10:5 -t 10:6.5 | |
| Mortgage duration: 30 years | |
| Principal amount: 250000 | |
| Gross yearly salary: 50000 | |
| Yearly salary bump: 3% | |
| 30% ruling for: 5 years | |
| Interest terms: | |
| 10 years @ 3.25% | |
| 10 years @ 5.00% | |
| 10 years @ 6.50% | |
| If we take the difference in net yearly mortgage payments and calculate | |
| an ROI of 5%, we can see which type of mortgage is cheaper in the long | |
| run if you are investing your spare income in index funds with a 5% return. | |
| The annuity mortgage has a higher net payment total than a linear mortgage, | |
| but the extra investment returns can more than make up for it due to the | |
| annuity mortgage's higher ratio of (tax-deductible) interest to principal | |
| payments, especially in the early years. | |
| Annuity investment earnings: 98331 | |
| Linear investment earnings: 65233 | |
| Annuity | |
| ------------------ | |
| Gross cost: 445536 | |
| Deduction: 72873 | |
| Net cost: 372662 | |
| Year 1 (3.25%) Repayment: 4926 Interest: 8052 Gross: 12978 Deduction: 873 Net: 12104 Avg monthly gross: 1081 Avg monthly net: 1008 | |
| Year 2 (3.25%) Repayment: 5088 Interest: 7889 Gross: 12978 Deduction: 1037 Net: 11940 Avg monthly gross: 1081 Avg monthly net: 995 | |
| Year 3 (3.25%) Repayment: 5256 Interest: 7721 Gross: 12978 Deduction: 1348 Net: 11629 Avg monthly gross: 1081 Avg monthly net: 969 | |
| Year 4 (3.25%) Repayment: 5429 Interest: 7548 Gross: 12978 Deduction: 1670 Net: 11307 Avg monthly gross: 1081 Avg monthly net: 942 | |
| Year 5 (3.25%) Repayment: 5609 Interest: 7369 Gross: 12978 Deduction: 2003 Net: 10975 Avg monthly gross: 1081 Avg monthly net: 914 | |
| Year 6 (3.25%) Repayment: 5794 Interest: 7184 Gross: 12978 Deduction: 2981 Net: 9996 Avg monthly gross: 1081 Avg monthly net: 833 | |
| Year 7 (3.25%) Repayment: 5985 Interest: 6992 Gross: 12978 Deduction: 2902 Net: 10076 Avg monthly gross: 1081 Avg monthly net: 839 | |
| Year 8 (3.25%) Repayment: 6182 Interest: 6795 Gross: 12978 Deduction: 2820 Net: 10158 Avg monthly gross: 1081 Avg monthly net: 846 | |
| Year 9 (3.25%) Repayment: 6386 Interest: 6591 Gross: 12978 Deduction: 2735 Net: 10242 Avg monthly gross: 1081 Avg monthly net: 853 | |
| Year 10 (3.25%) Repayment: 6597 Interest: 6380 Gross: 12978 Deduction: 2648 Net: 10330 Avg monthly gross: 1081 Avg monthly net: 860 | |
| Year 11 (5.00%) Repayment: 5613 Interest: 9509 Gross: 15122 Deduction: 3946 Net: 11176 Avg monthly gross: 1260 Avg monthly net: 931 | |
| Year 12 (5.00%) Repayment: 5900 Interest: 9222 Gross: 15122 Deduction: 3827 Net: 11295 Avg monthly gross: 1260 Avg monthly net: 941 | |
| Year 13 (5.00%) Repayment: 6202 Interest: 8920 Gross: 15122 Deduction: 3702 Net: 11420 Avg monthly gross: 1260 Avg monthly net: 951 | |
| Year 14 (5.00%) Repayment: 6519 Interest: 8603 Gross: 15122 Deduction: 3570 Net: 11552 Avg monthly gross: 1260 Avg monthly net: 962 | |
| Year 15 (5.00%) Repayment: 6852 Interest: 8269 Gross: 15122 Deduction: 3431 Net: 11690 Avg monthly gross: 1260 Avg monthly net: 974 | |
| Year 16 (5.00%) Repayment: 7203 Interest: 7919 Gross: 15122 Deduction: 3286 Net: 11836 Avg monthly gross: 1260 Avg monthly net: 986 | |
| Year 17 (5.00%) Repayment: 7572 Interest: 7550 Gross: 15122 Deduction: 3133 Net: 11989 Avg monthly gross: 1260 Avg monthly net: 999 | |
| Year 18 (5.00%) Repayment: 7959 Interest: 7163 Gross: 15122 Deduction: 2972 Net: 12149 Avg monthly gross: 1260 Avg monthly net: 1012 | |
| Year 19 (5.00%) Repayment: 8366 Interest: 6756 Gross: 15122 Deduction: 2803 Net: 12318 Avg monthly gross: 1260 Avg monthly net: 1026 | |
| Year 20 (5.00%) Repayment: 8794 Interest: 6327 Gross: 15122 Deduction: 2626 Net: 12496 Avg monthly gross: 1260 Avg monthly net: 1041 | |
| Year 21 (6.50%) Repayment: 8797 Interest: 7655 Gross: 16452 Deduction: 3176 Net: 13275 Avg monthly gross: 1371 Avg monthly net: 1106 | |
| Year 22 (6.50%) Repayment: 9386 Interest: 7066 Gross: 16452 Deduction: 2932 Net: 13520 Avg monthly gross: 1371 Avg monthly net: 1126 | |
| Year 23 (6.50%) Repayment: 10015 Interest: 6437 Gross: 16452 Deduction: 2671 Net: 13781 Avg monthly gross: 1371 Avg monthly net: 1148 | |
| Year 24 (6.50%) Repayment: 10685 Interest: 5766 Gross: 16452 Deduction: 2393 Net: 14059 Avg monthly gross: 1371 Avg monthly net: 1171 | |
| Year 25 (6.50%) Repayment: 11401 Interest: 5051 Gross: 16452 Deduction: 2096 Net: 14356 Avg monthly gross: 1371 Avg monthly net: 1196 | |
| Year 26 (6.50%) Repayment: 12165 Interest: 4287 Gross: 16452 Deduction: 1779 Net: 14673 Avg monthly gross: 1371 Avg monthly net: 1222 | |
| Year 27 (6.50%) Repayment: 12979 Interest: 3472 Gross: 16452 Deduction: 1441 Net: 15011 Avg monthly gross: 1371 Avg monthly net: 1250 | |
| Year 28 (6.50%) Repayment: 13849 Interest: 2603 Gross: 16452 Deduction: 1080 Net: 15372 Avg monthly gross: 1371 Avg monthly net: 1281 | |
| Year 29 (6.50%) Repayment: 14776 Interest: 1676 Gross: 16452 Deduction: 695 Net: 15757 Avg monthly gross: 1371 Avg monthly net: 1313 | |
| Year 30 (6.50%) Repayment: 15766 Interest: 686 Gross: 16452 Deduction: 284 Net: 16167 Avg monthly gross: 1371 Avg monthly net: 1347 | |
| Linear | |
| ------------------ | |
| Gross cost: 407803 | |
| Deduction: 56805 | |
| Net cost: 350998 | |
| Year 1 (3.25%) Repayment: 8333 Interest: 8000 Gross: 16334 Deduction: 868 Net: 15466 Avg monthly gross: 1361 Avg monthly net: 1288 | |
| Year 2 (3.25%) Repayment: 8333 Interest: 7730 Gross: 16063 Deduction: 1020 Net: 15042 Avg monthly gross: 1338 Avg monthly net: 1253 | |
| Year 3 (3.25%) Repayment: 8333 Interest: 7459 Gross: 15792 Deduction: 1319 Net: 14472 Avg monthly gross: 1316 Avg monthly net: 1206 | |
| Year 4 (3.25%) Repayment: 8333 Interest: 7188 Gross: 15521 Deduction: 1631 Net: 13889 Avg monthly gross: 1293 Avg monthly net: 1157 | |
| Year 5 (3.25%) Repayment: 8333 Interest: 6917 Gross: 15250 Deduction: 1954 Net: 13296 Avg monthly gross: 1270 Avg monthly net: 1108 | |
| Year 6 (3.25%) Repayment: 8333 Interest: 6646 Gross: 14980 Deduction: 2758 Net: 12221 Avg monthly gross: 1248 Avg monthly net: 1018 | |
| Year 7 (3.25%) Repayment: 8333 Interest: 6375 Gross: 14709 Deduction: 2645 Net: 12063 Avg monthly gross: 1225 Avg monthly net: 1005 | |
| Year 8 (3.25%) Repayment: 8333 Interest: 6105 Gross: 14438 Deduction: 2533 Net: 11904 Avg monthly gross: 1203 Avg monthly net: 992 | |
| Year 9 (3.25%) Repayment: 8333 Interest: 5834 Gross: 14167 Deduction: 2421 Net: 11746 Avg monthly gross: 1180 Avg monthly net: 978 | |
| Year 10 (3.25%) Repayment: 8333 Interest: 5563 Gross: 13896 Deduction: 2308 Net: 11587 Avg monthly gross: 1158 Avg monthly net: 965 | |
| Year 11 (5.00%) Repayment: 8333 Interest: 8142 Gross: 16475 Deduction: 3379 Net: 13096 Avg monthly gross: 1372 Avg monthly net: 1091 | |
| Year 12 (5.00%) Repayment: 8333 Interest: 7725 Gross: 16059 Deduction: 3206 Net: 12852 Avg monthly gross: 1338 Avg monthly net: 1071 | |
| Year 13 (5.00%) Repayment: 8333 Interest: 7309 Gross: 15642 Deduction: 3033 Net: 12609 Avg monthly gross: 1303 Avg monthly net: 1050 | |
| Year 14 (5.00%) Repayment: 8333 Interest: 6892 Gross: 15225 Deduction: 2860 Net: 12365 Avg monthly gross: 1268 Avg monthly net: 1030 | |
| Year 15 (5.00%) Repayment: 8333 Interest: 6475 Gross: 14809 Deduction: 2687 Net: 12121 Avg monthly gross: 1234 Avg monthly net: 1010 | |
| Year 16 (5.00%) Repayment: 8333 Interest: 6059 Gross: 14392 Deduction: 2514 Net: 11877 Avg monthly gross: 1199 Avg monthly net: 989 | |
| Year 17 (5.00%) Repayment: 8333 Interest: 5642 Gross: 13975 Deduction: 2341 Net: 11634 Avg monthly gross: 1164 Avg monthly net: 969 | |
| Year 18 (5.00%) Repayment: 8333 Interest: 5225 Gross: 13559 Deduction: 2168 Net: 11390 Avg monthly gross: 1129 Avg monthly net: 949 | |
| Year 19 (5.00%) Repayment: 8333 Interest: 4809 Gross: 13142 Deduction: 1995 Net: 11146 Avg monthly gross: 1095 Avg monthly net: 928 | |
| Year 20 (5.00%) Repayment: 8333 Interest: 4392 Gross: 12725 Deduction: 1822 Net: 10902 Avg monthly gross: 1060 Avg monthly net: 908 | |
| Year 21 (6.50%) Repayment: 8333 Interest: 5168 Gross: 13501 Deduction: 2144 Net: 11356 Avg monthly gross: 1125 Avg monthly net: 946 | |
| Year 22 (6.50%) Repayment: 8333 Interest: 4626 Gross: 12960 Deduction: 1920 Net: 11039 Avg monthly gross: 1080 Avg monthly net: 919 | |
| Year 23 (6.50%) Repayment: 8333 Interest: 4085 Gross: 12418 Deduction: 1695 Net: 10723 Avg monthly gross: 1034 Avg monthly net: 893 | |
| Year 24 (6.50%) Repayment: 8333 Interest: 3543 Gross: 11876 Deduction: 1470 Net: 10406 Avg monthly gross: 989 Avg monthly net: 867 | |
| Year 25 (6.50%) Repayment: 8333 Interest: 3001 Gross: 11335 Deduction: 1245 Net: 10089 Avg monthly gross: 944 Avg monthly net: 840 | |
| Year 26 (6.50%) Repayment: 8333 Interest: 2460 Gross: 10793 Deduction: 1020 Net: 9772 Avg monthly gross: 899 Avg monthly net: 814 | |
| Year 27 (6.50%) Repayment: 8333 Interest: 1918 Gross: 10251 Deduction: 796 Net: 9455 Avg monthly gross: 854 Avg monthly net: 787 | |
| Year 28 (6.50%) Repayment: 8333 Interest: 1376 Gross: 9710 Deduction: 571 Net: 9138 Avg monthly gross: 809 Avg monthly net: 761 | |
| Year 29 (6.50%) Repayment: 8333 Interest: 835 Gross: 9168 Deduction: 346 Net: 8821 Avg monthly gross: 764 Avg monthly net: 735 | |
| Year 30 (6.50%) Repayment: 8333 Interest: 293 Gross: 8626 Deduction: 121 Net: 8504 Avg monthly gross: 718 Avg monthly net: 708 | |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment