Lesson Using Excel to find the interest part of a certain loan payment

Using Excel to find the interest part of a certain loan payment

Problem 1

Let's make the timeline

    July 1, 2000  Peter borrows $7600   The debt becomes $7600 

    July 1, 2001  Peter borrows $7600   The debt becomes $15200 

    July 1, 2002  Peter borrows $7600   The debt becomes $22800

    July 1, 2003  Peter borrows $7600   The debt becomes $30400

    July 1, 2004  Peter found the job   The debt is      $30400 and Peter is charged 8% compounded semiannually from this date


So, starting from July 1, 2004 we have classic loan problem:

    the loan amount is $30400; it is 8% charged semiannually;
    the debt should be repaid in 14 equal semiannual payments (two payments per year), 
    in 7 years (2005, 2006, 2007, 2008, 2009, 20010, 2011).

Make one more timeline

    1-st payment   Jan 1, 2005   first payment comes 6 months after he starts his job
    2-nd payment   Jul 1, 2005
    3-rd payment   Jan 1, 2006
    4-th payment   Jul 1, 2006
    5-th payment   Jan 1, 2007
    6-th payment   Jul 1, 2007


They want you determine the total amount of interest Peter pays in 2007.

So, they want you find the sum of the interest portions of the 5-th and 6-th payments.


Use the Excel function IPMT, which is specially designed to calculate the interest part 
of a classic loan scheme for the specific payments.


For description of this function see these sources

    wording/text description    
    https://www.wallstreetprep.com/knowledge/ipmt-function/

    Youtube videos
    https://www.youtube.com/watch?v=xZq4RNqE7ts
    https://www.youtube.com/watch?v=bni0l75lc-8


The format of the function IPMT is as follows

    = IPMT(rate, per, nper, principal, fv, type)


In this format "rate" is the rate per period = 0.04   (= 0.08/2 semiannually)         required parameter

                per  =  5  - the payment, of which we want to get the interest part;  required parameter

                nper = 14  - total number of payments;                                required parameter 

                principal = 30400;                                                    required parameter

                fv                                                                    is optional (we can omit it);

                type                                                                  is optional (we can omit it, which means that 
                                                                                         the payment is due at the end of the half-year period)


So, we write in Excel cell/spreadsheet for the interest part of the 5-th payment

    = IPMT(0.04, 5, 14, 30400),  and we get the value of the interest for the 5-th payment of $933.71.


Next, we write in Excel cell/spreadsheet for the interest part of the 6-th payment

    = IPMT(0.04, 6, 14, 30400),  and we get the value of the interest for the 6-th payment of $855.64.


So, total interest part of the two Peter's payments of the year 2007 is the sum  $933.71 + $855.64 = $1,789.64.    ANSWER

Problem 2

The solution is in three steps.  First, I determine the annual payment.
Then I determine a series of interest portion of the annual payments.
Then I look which of the interest portions is most nearly equal to one-third of the payment.


Use the formula for the annual payment for a loan

    Y = 


where L is the loan amount; r = 0.08 is the annual interest rate;
n is the number of payments (in this problem it the same as the number of years); 
Y is the annual payment.


In this problem  P = $10,000;  r = 0.08,  n = 30 annual payments.


Substitute these values into the formula and get for monthly payment

    Y =  = $888.27.


Thus, the annual payment is $888.27.


(1/3) of the annual payment is  888.27/3, or 296.09 dollars.


To determine the interest portions of annual payments, I use Excel function IPMT.


    For the description of this function, its parameters and syntaxis, see these sources

        wording/text description    
        https://www.wallstreetprep.com/knowledge/ipmt-function/

        Youtube videos
        https://www.youtube.com/watch?v=xZq4RNqE7ts
        https://www.youtube.com/watch?v=bni0l75lc-8



The table of results is shown below

Year        Interest part     the difference
            of the annual     between column 2
              payment         and 296.09 dollars
----------------------------------------------------
 1		800.00		503.91
 2		792.94		496.85
 3		785.31		489.22
 4		777.07		480.98
 5		768.18		472.09
 6		758.57		462.48
 7		748.19		452.10
 8		736.99		440.90
 9		724.88		428.79
10		711.81		415.72
11		697.70		401.61
12		682.45		386.36
13		665.98		369.89
14		648.20		352.11
15		629.00		332.91
16		608.25		312.16
17		585.85		289.76
18		561.66		265.57
19		535.53		239.44
20		507.31		211.22
21		476.83		180.74
22		443.92		147.83
23		408.37		112.28
24		369.97		 73.88
25		328.51		 32.42
26		283.73		 12.36    <<<---===
27		235.37		 60.72
28		183.13		112.96
29		126.72		169.37
30		 65.80		230.29


The minimum value in the third column is marked by  <<<---===.

It is 26-th year payment.


ANSWER.  The minimum difference is in the 26-th year payment.