Question

How do I amortize a mortgage loan with daily compounded AND monthly compunded interest with bi-weekly payments in excel?

Let's use the example:

Loan Amount - $100,000.00

Rate - 4.0%

Period - 30 years (fixed)

Please provide both solutions. Thanks.

Answer 1

Ans. The compounding plays a big role in the EMI a person will pay on the loan. The more the compounding the more we will pay. In excel the formula used is of pmt. Where pmt=(rate,nper,pv,fv,type)

Rate= Interest rate (adjusted with the frequency)

The frequencies for the three conditions are as follows:

Daily: 365

Monthly= 12

Bi-Weekly: 26

n= number of compounding (also adjusted with frequency of payment)

pv, fv= present value, future value

type (ending or beining) Here it is begining

For Daily

For daily the interest rate will be 4%/365 =.0011 to calculate the per day interest.

N= 30*365 (number of times the compounding will happen)

Loan Amount	Rate	Period
-100000	4%	30
Per Day Interest	0.00011%

		Daily
Loan Amount	Principle	Interest	EMI
100000	$4.72	10.9589	$15.68

Monthly

In monthly the per month interest will be 4%/12=.333%

The Nper=10950

Loan Amount	Rate	Period
-100000	4%	30
Per month Interest	0.333333%

		Monthly
Loan Amount	Principle	Interest	EMI
100000	$142.50	333.3333	$475.83

Bi-Weekly

In bi-weekly the Rate of interest will be 4%/26 = .15%

Nper=780

Loan Amount	Rate	Period	Bi Weekly
-100000	4%	30	26

		Bi-Weekly
Loan Amount	Principle	Interest	EMI
100000	$66.06	153.8462	$219.91

Based on all the three conditions few things which can be concluded are :

In daily the payment made will be more, as the compounding is more, even though interst portion seems to be less, but on total basis the interest payment made will be more.

The Monthly has biggest sum of money becuase of the duration being less compare to other two. Here the payment made is once in a month. This is followed by bi-weekly

The interest payment will always be more at the begining compare to principle.