Question

Jan sold her house on December 31 and took a $30,000 mortgage as part of the payment. The 10-year mortgage has a 6% nominal interest rate, but it calls for semiannual payments beginning next June 30. Next year Jan must report on Schedule B of her IRS Form 1040 the amount of interest that was included in the two payments she received during the year. (Please show the excel function to find this)

1) How would I find the dollar amount of each payment Jan received?

2) How much interest was included in the first payment?

3) How much repayment of principal was included?

Answer 1

1) Present Value (PV) = $30,000

Interest Rate (R) = 6%

Time in Years (t) = 10 Years

Compounding Periods (n) = Semiannualy ( 2 Times per year)

Required Annuity Payments (PMT)

     PMT =

              =

              =   

              =

2) First payment-Interest calculation

    Interest include the first payment is equal to the present value of mortgage ( (now still at $30,000) multiplied by periodic interest rate of 3% (6%/2)

Interest =$30,000*0.03 =$900

3)   The payment that goes to principal is equal to the monthly payment less interest =$2,016.58-$900 = $1,116.58

After the first payment remaining value of mortgage is

$30,000 - $1,116.58 = $28,883.42

That amount will be the basis of next period

Interest = $28,883.42*0.03 = $866.50

Reduction of principal

=$2,016.58 - $866.50   = $1,150.08

The execel function to find this annuity Payment is  =PMT(rate,nper,pv,fv,type)

RATE is the discount rate or interest rate, NPER is the number of periods with that discount rate, PV is present value and FV is future value

For this case

Rate = 6%/2 = 3% (semi annually)=0.03

nper= 10*2

PV = $30,000

Fv and Type is not necessary in this case ,

eg =PMT(0.03,20,30,000)