Formula for Fixed & Reducing Balance Loan Calculation

[Bala!aditya]

Could anyone, please guide me following two questions.

• Fixed Annual Interest Rate
• Reducing Balance Annual Interest Rate
• Principal Amount : 800,000
• Profit Rate: 5.9 % Per Anum
• Years: 20

Advise formula for both type of calculation, to know installment per month or per year basis. I need to find out the interest portion and principal portion for each month using reducing balance.. would be gr8 if you can provide the formula

[Shurod]

If you make monthly payments __and__ we are talking about a loan that is amortized similar to US loans (specifically, not Canadian loans), the minimum monthly payment can be determined by the following, assuming a monetary system similar to the US dollar (i.e. with the smallest coin equal to 1/100 of the currency unit):
=roundup(pmt(5.9%/12, 20*12, -800000), 2)
But please note that different countries might compute the monthly rate (he 5.9%/12) differently. For example, I have noticed that another method is used in the UK, at least according to some lenders web sites. As for a per-year basis, I am not quite sure what you are asking. If you make monthly payments, obviously you pay 12 times that in a year. If you make annual payments (surprise!), then with all the aforementioned assumptions, the annual payment would be:
=roundup(pmt(5.9%, 20, -800000), 2)
Caveats:
Some lenders might permit rounding down. And some lenders prefer to round up or down to a "dollar" or whatever the currency unit of the region is. In rare circumstances, rounding up (usually to a much higher degree, for example 10s of "dollars") might result in fewer payments. You can compute the number of payments as follows (for monthly payments):
=roundup(nper(5.9%/12, p, -800000), 0)
Rounding the payment will usually result in a somewhat different last payment. You can determine the last payment by the following (for monthly payments):
=fv(5.9%/12, n - 1, p, -800000)*(1+5.9%/12)
where "p" is the rounded monthly payment computed above and "n" is the number of periods computed in #2 or simply 20*12.

[Erakna]

I have a example by which I can help to create a Excel mortgage model using cell in the top of the spreadsheet. With the below method you calculate the Amount of the loan, Interest rate and Number of periods. I had defined the colors which can help to compare the value of loans. On the left side create a payment column. For a large number such as 30 credits per year, it is easier to use the "fill handle" and let Excel fill in the numbers for you. Create columns for information like Loan opening balance, Payment, Amount requested to the interests, Amount applied to Capital and Loan residual.

The next thing you can do is tell Excel to calculate the value. Remember to use the "$" when referring to any line number in the calculations, except for the period - otherwise Excel will appear in the wrong line. Use the PMT function to calculate your monthly payment. You can use the IPMT function to get the amount of each payment which goes to interest. Then minus the amount of interest from the total payment so that you can get the figure of how much you paid (principal). Once done with that subtract the principal from the balance of the loan balance to arrive at your new loan. Repeat the same for ever month.