Dec 112017
 
Amortization Calculator and Table.
File VBAMORT.ZIP from The Programmer’s Corner in
Category Recently Uploaded Files
Amortization Calculator and Table.
File Name File Size Zip Size Zip Type
AMORTIZE.EXE 197588 121260 deflated
README.TXT 20811 5978 deflated

Download File VBAMORT.ZIP Here

Contents of the README.TXT file


Amortization Calculator and Table.
Created by MJW Services.
(c) MJW Services 1993



This text file will provide useful examples on how to use the
software to solve complex financial problems. The software is not
the only item which is needed to complete these types of problems.
A pencil and paper for note taking will help create a powerful
financial tool useful for many different situations.


The text file is broken down into (6) catagories as follows

1. Special Notes

2. Amortization of a debt.

3. Mortgages.

4. Calculating the Outstanding Principal and Total Interest on debt.

5. Refinancing a loan.

6. Credit cards.




1. Special Notes


Disclaimer, Warranty and Copyright
----------------------------------

While this software is easy to use, financial planning requires
careful examination. MJW Services specifically disclaims all expressed
or implied warranties, including but not limited to, implied warranties
of merchantability and fitness for a particular use. In no event
shall MJW Services be liable for any loss of profit or any other
commercial damage. You should obtain professional guideance when making
any major financial decisions. MJW Services will not be responsible
for your interpretations of the results obtained with these routines,
even, in the unlikely event, it is shown that there is an error in the
programming of a particular routine. If you are about to make what
could be for you an important financial decision, always carefully
recheck the results obtained with this or any calculator.

For Non-Demo version only
-------------------------

This software is protected by Canadian copyright law. You must
treat this software like a "book", except you can make backup copies
for the purpose of protecting it from loss. Treating the software
like a "book" means that it can be used by anybody, and can be moved
from one computer to another, but just as a book cannot be used by
two people, at two different places, at the same time, neither can
this software (unless the copyright has been violated).



Calculation Notes
-----------------

Please keep in mind that the calculations happening inside
your machine are not rounded. All of the internal variables can
hold 15 or 16 digit numbers when the calculations are being performed
(only the digits being displayed on the screen are rounded, decimals
0 to 4 are rounded down and 5 to 9 are rounded up). When the table
is displayed, the numbers in each coloumn are rounded but that number
(which is used for the next calculation internally within the "loop"
is not rounded). When you calculate the Payment amount, the Number
of Payments final amount or the FVPrincipal ,the dollar figures are
displayed showing 4 digit decimals. This is necessary so that you are
able to perform the common financial practice of rounding up to the
nearest cent, regardless of the decimal (unless the 10th and 100th of
a cent decimals are 00 of course). You can still round down for
0 to 4 and up for 5 to 9 if you wish.
The results are very accurate.


Remember, an Amortization is a series of periodic payments,
usually equal, being made to reduce an outstanding debt. It is assumed
in this program, that the first payment being made on the loan
amount will be made exactly 1 payment period after the loan is issued.
The payment period is determined by the 'Payment Made' option.
If this procedure is not followed, the Dates on the Amortization Table
and the Amounts in the table will not be correct.

For the calculator, turning the Num Lock on and using the number
keypad is the easiest way to use it. The 'Backspace' key works as the
clear/clear error key and 'Enter' can be used for Equal.

You can use the Scrolling help box as an area where you can
take little notes, record numbers, etc. Don't worry about erasing
any words inside the box; they will be restored whenever you
run the program. Keep in mind that any notes you put inside of
the box WILL NOT be saved when you exit the program.


The examples contained within this text file will provide you
with some insight on how to reach an accurate solution using this
program. Only certain portions of the following can be calculated
with the demo version.


2. Amortization of a debt

The Amortization method is used to eliminate an interest bearing
debt by making a series of periodic payments (usually equal payments).
When a debt is amortized by equal payments at equal payment intervals,
the debt is actually the discounted value of an annuity. The size of
the payment is determined by annuity methods.

eg.1. A loan of $10000 is to be amortized with equal monthly
payments over a period of 10 years. The interest rate
is 10% compounded monthly. Find the value of the
concluding payment.

solution: Enter information into appropriate fields.
Principal = 10000
Interest rate = .10
Number of payments = 12 payments x 10 years = 120
Select Rate Compounded Monthly option and
Payment Made monthly option.

Tab to Payment Amount command ( or Alt-a to immediately
execute the command) and press Enter.
The result is $132.1507. Rounding up to the nearest cent
we get $132.16. Tab to the Payment input field( or Alt-y to
immediately jump to it) and enter the number 132.16. Tab to
the Number of Payments command (or Alt-u to immediately
execute the command) and press enter. The result is 119
equal payments of $132.16 and 1 final payment of $130.2625.
Rounding the final payment up to the nearest cent, the final
payment is $130.27. Verify this result with what the
Amortization Table displays and you can see that the 120th
final payment is $130.27 (129.19 + 1.08).

eg.2. A loan of $5000 will be repaid over 3 years by making quarterly
payments. The interest rate is 12% compounded monthly. Find
the quarterly payment and show the interest and principal
portion of each payment.

solution: Enter information into appropriate fields.
Principal = 5000
Interest rate = .12
Number of payments = 4 payments x 3 years = 12
Select Rate Compounded Monthly option and
Payment Made quarterly option.

Tab to Payment Amount command ( or Alt-a to immediately
execute the command) and press Enter.
The result is $503.2134 (rounding up to the nearest cent
we get $503.22).Tab to the Table command and press Enter.
The program will ask you to enter the date when
the payments begin. By default, the program will display
your system date, so just Tab to the OK button and press
enter if the date is not important. We can see the
interest portion and the principal repayment for each payment.


eg.3. A $3000 loan will be repaid by monthly payments of $600 for as
long as necessary with the first payment being made at the end
of 6 months. The interest rate is 13% compounded monthly. Find
the size of the debt at the end of 5 months and produce an
Amortization Table.

solution: Enter information into appropriate fields.
Principal = 3000
Interest rate = .13
Number of payments = 5
Select Rate Compounded Monthly option and
Payment Made monthly option.

Tab to the FVPrincipal command (Future Value of a Principal,
or Alt-f to immediately execute) and press Enter.
The result is $3166.0592 (rounding up to the nearest cent
we get $3166.06). This amount is the value of $3000 at 13%
interest compounded monthly for 5 months. The payments have
been deferred for 6 months but the interest will still
accumulate for the 5 months prior to the start of the
amortization. $3166.06 is the amount of the loan which will
be amortized by making monthly payments of $600 for as
long as necessary. Enter 3166.06 as the Principal amount
of the loan. Enter 600 as the amount of the Payment on
the loan each period (the other information will remain the
same since the rate, compounding period, or payment made time
period are not changing). Tab to the Number of Payments
button and press Enter. The result is 5 equal payments of
$600 and 1 payment of $278.5888(rounding up to the nearest
cent we get $278.59). Tab to the Table button that has
appeared and press Enter to view the Amortization Table.



3. Mortgages

This section will give insight into using the software to calculate
Mortgages in Canada.

eg.1. In mid 1979, mortgage rates in Canada averaged around 11%
compounded semi-annually. Two years later, interest rates
rose to 22% compounded semi-annually. On a mortgage of
$40000, determine the monthly payment over a 20 year
mortgage for both rates of interest.

solution: Enter the information for the first mortgage.
Principal = 40000
Interest Rate = .11
Number or Payments = 12 payments x 20 years = 240
Select Rate Compounded Semi-Annually and Payments Made
monthly options.

Tab to Payment Amount and press Enter (or Alt-a to
immediately execute). The result is $406.2559 or rounded
$406.26 per month.

Enter the information for the second mortgage.
All information remains the same except for:
Interest Rate = .22
Enter this amount then tab to Payment Amount.
The result is $712.7850 or rounded up $712.79 per month.


eg.2. A couple buys a house worth $102000 by paying $52000 down and
taking a mortgage for $50000. The interest rate is 9%
compounded semi-annually, and they will make monthly payments
over a 25 year period. Determine the amount of the debt paid
off in the first year.

solution: Enter the information for the problem
Principal = 50000
Interest Rate = .09
Number of Payments = 12 payments x 25 years = 300
Selct Interest Compounded Semi-Annually and Payments
Made Monthly.

Tab over to (or press Alt-a to immediately execute) the
Payment Amount button and press Enter. The monthly
payment is $413.9887 (or $413.99 rounded up). Tab to the
Table button that has appeared on the screen and press
Enter to display the Amortization Table. We can see
the Ending Principal amount for the 12th payment is
$49427.18. Subtracting this number from the original
Principal amount we get $50000 - $49427.18 = $572.82
which is the amount of Principal paid off after the
first year.

4. Calculating the Outstanding Principal and Total Interest at a point
in time.

The Amortization Table is very useful for calculating Outstanding
Principal amounts, Principal Repayment and Interest charged,
and Total Interest paid at a particular point in time. The
following example will show you how to use these calculations.

eg.1 A car is purchased for $15000 by paying $6000 down and then
equal monthly payments for 3 years at 15% interest compounded
monthly. Find the Outstanding Principal Amount and the
amount of interest paid after the first year.

solution: Enter the information:
Principal Amount = 15000 - 6000 = 9000
Interest rate = .15
Number of Payments = 12 payments x 3 years = 36
Select Interest Compounded monthly and Payments made
monthly.

Tab over to the Payment Amount button and press
Enter. The monthly payment will be $311.9880(rounded up
$311.99. Tab over to the Table button that has appeared
and press Enter to display the Amortization Table.
The Ending Principal at the 12th payment is $6434.51 and
the Total Interest paid on the loan at the 12th payment
is $1178.37.


5. Refinancing a loan

The power of this software is greatly increased by the
user's ability to logically pick out all the information
to complete a complex financial problem. Once it is broken
down into smaller parts, knowing what to do with the parts
is the key to solving a difficult problem. Knowledge of financial
mathematics is not required to arrive at the correct solution
(the program takes care of that for you). The following
questions will demonstrate how to use the software to determine
whether refiancing a loan is the correct option to pick.


eg.1. Fred buys $6500 worth of furniture from Joe's Furniture Store.
He pays $500 down and agrees to pay off the balance with
monthly payments over 5 years. The interest rate Joe's
Furniture Store charges is 16% compounded monthly. The
payment agreement states that if Fred wishes to pay off the
remaining balance early, a penalty will be charged equal to
3 months' payments. After 2 years, Fred determines
that he can borrow the money from a bank at 10% compounded
monthly, but will also have to pay the 3 month penalty.
Should he refinance?

solution: The first thing which needs to be done to solve this
problem will be to enter the information required
to determine the size of the monthly payments Fred
will be required to pay over the 5 year period.

Principal = 6500 - 500 = 6000
Interest Rate = .16
Number of Payments = 12 payments x 5 years = 60
Select Interest compounded monthly and Payment
made monthly.

Tab over to the Payment Amount and press Enter (
or Alt-a to immediately execute). The monthly
payment amount is $145.9083 (rounded up $145.91).

We can now determine the penalty that Fred will have
to pay if he pays off the balance owing to the
furniture company early.

$145.91 x 3 = $437.73 is 3 months worth of payments
and is the amount of the penalty.

After paying $145.91 per month for 2 years, Fred realizes
that he can borrow money from the bank at a lower rate.
Here, we Tab to the Table button that has appeared and
press enter to display the Amortization Table. At the
24th monthly payment, the ending balance of the loan is
$4150.19. Since paying off this amount will result in
the penalty being assessed, we will add the penalty amount
to the ending balance of the loan after 2 years to
determine the amount of money to be borrowed from the bank.

$4150.19 + 437.73 = $4587.92

The $4587.92 is the amount that now will be amortized over
the remaining 3 years (since 2 years have already been
paid off of the 5 year monthly payment schedule).

Scroll the Amortization Table to the end and re-enter the
new information into the program.

Principal = 4587.92
Interest Rate = .10
Number of Payments = 12 payments x 3 years = 36
The compound period remains the same as does the
monthly payment.

Tab to the Payment Amount and press Enter.
We can see that the new Payment amount is
$148.0393 (rounded up $148.04).
It would not be a wise financial move for Fred
to refinance the loan since his monthly payment
will go up by $2.13. ($148.04 - $145.91)


eg.2. A five year bank loan for $8500 is being amortized with monthly
payments at 15% compounded monthly. Just after making the 30th
payment, the borrower has the remaining principal refinanced
at 11% compounded monthly with the term of the loan remaining
unchanged. Find the monthly savings in interest.

solution: Enter the information:
Principal = 8500
Interest Rate = .15
Number of Payments = 12 payments x 5 years = 60
Select Interest compounded monthly and Payments Made
monthly options.

Tab to Payment Amount and press Enter.
The monthly payment is $202.2144(rounded up 202.22).
Tab to the Table button that has appeared and press
Enter to display the Amortization Table.
After the 30th payment, the Ending Principal is
$5032.90. This is the amount to be refinanced at
11%

Enter the new information:
Principal = 5032.90
Interest Rate = .11
Number of payments = 30
Compound period and Payment period do not change.
Tab to the Payment Amount and press Enter to see
the new monthly payment. It is $192.6497 (rounded
up $192.65)

Now we can find the monthly savings in interest
$202.22 - $192.65 = $9.57



6. Credit Cards

Interest rates on Credit Card balances outstanding are
compounded daily. The higher the frequency of the compounding,
given a fixed Principal amount and Interest rate, the faster
the interest accumulates.


eg.1. John has a credit card that charges interest at 16.75% compounded
daily. On March 17th, he uses the card at a bank machine
for a cash advance of $400. If the statement date is April 15th,
what will be the balance outstanding on the credit card as of
that date (assuming no other charges have been incurred and
no fee has been assessed by the bank for a cash advance)?
If he pays $40 per month on the credit card, how long will
it take for him to pay off the balance and what is the
amount of the final payment?

Solution: Enter the information
Principal = 400
Interest Rate = .1675
Number of Periods = 30
Day 1 is the day the cash was advanced (Mar 17) and day 30
is the statement date ( April 15)
Select the Rate Compounded daily option.

Remember, we are calculating the Future value of an amount
so we will be using the FVPrincipal button for the
calculation. When this button is used, the Number of
Payments input area is actually the number of
interest compound periods.

Tab to the FVPrincipal button and press Enter.
The result is $405.5436 rounded up is $405.55 which is
the balance on the card.

Remember that the digits used internally for this program
(and the daily interest rate) are 15 or 16 digits long
so the result is very accurate. Companies that issue
credit cards sometimes round off the daily interest
compounding rate to 5, 6, or 7 digits. When this is
the case, what you see on your statement as interest
charges may vary slightly from the results produced
by this software.

Input the information back into the program
Principal = 405.55
Interest rate = .1675
Payment amount = 40
Make sure the interest compounded period is set to daily
and the payments made option button is set to monthly.
Tab to the Number of Payments button and press Enter.

The result is 11 equal payments of $40 and 1 payment of
59 cents.





 December 11, 2017  Add comments

Leave a Reply