Mortgage formula

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

How to calculate monthly repayment in Access for this following mortgage loan
on a multi tier rates environment (calculated on monthly basis)

Principle Amt - 100,000
Tenure - 25 years
Interest rates 1st year - 1.50%
2nd year - 5.00%
3rd year - 6.25%

Monthly repayment 1st year - ?
2nd year - ?
3rd year - ?

Appreciate your response.. ?
 
You will need to calculate the balance after the 1st, and 2nd years to
calculate the next year's payment:

-Calculate the payment for the first year:
Payment_1st_Year: Pmt([APR_Year_1]/12,[Years]*12,-[Principal])

-Calculate the balance after the 1st year:
Balance_After_1st_Year: FV([APR_Year_1]/12,12,[Payment_1st_Year],-[Principal])

-Calculate payment for 2nd year:
Payment_2nd_Year:
Pmt([APR_Year_2]/12,([Years]-1)*12,-[Balance_After_1st_Year])

-Calculate the balance after the 2nd year:
Balance_After_2nd_Year:
FV([APR_Year_2]/12,12,[Payment_2nd_Year],-[Balance_After_1st_Year])

-Calculate payment for 3rd year:
Payment_3rd_Year:
Pmt([APR_Year_3]/12,([Years]-2)*12,-[Balance_After_2nd_Year])
 
Thanks very much folk.. You've lighten up my tunnel...Thanks again

jl5000 said:
You will need to calculate the balance after the 1st, and 2nd years to
calculate the next year's payment:

-Calculate the payment for the first year:
Payment_1st_Year: Pmt([APR_Year_1]/12,[Years]*12,-[Principal])

-Calculate the balance after the 1st year:
Balance_After_1st_Year: FV([APR_Year_1]/12,12,[Payment_1st_Year],-[Principal])

-Calculate payment for 2nd year:
Payment_2nd_Year:
Pmt([APR_Year_2]/12,([Years]-1)*12,-[Balance_After_1st_Year])

-Calculate the balance after the 2nd year:
Balance_After_2nd_Year:
FV([APR_Year_2]/12,12,[Payment_2nd_Year],-[Balance_After_1st_Year])

-Calculate payment for 3rd year:
Payment_3rd_Year:
Pmt([APR_Year_3]/12,([Years]-2)*12,-[Balance_After_2nd_Year])



--
jl5000
<a href="http://www.joshdev.com"></a>


zyus said:
How to calculate monthly repayment in Access for this following mortgage loan
on a multi tier rates environment (calculated on monthly basis)

Principle Amt - 100,000
Tenure - 25 years
Interest rates 1st year - 1.50%
2nd year - 5.00%
3rd year - 6.25%

Monthly repayment 1st year - ?
2nd year - ?
3rd year - ?

Appreciate your response.. ?
Click to expand...
Click to expand...
 
Hi,

If after the 1st year the rate is fixed for 2nd year to 10th year, what will
be the formula to calculate the repayment for 11 year onwards.

For example (assume other items remain the same as last ex)

Thank you


jl5000 said:
You will need to calculate the balance after the 1st, and 2nd years to
calculate the next year's payment:

-Calculate the payment for the first year:
Payment_1st_Year: Pmt([APR_Year_1]/12,[Years]*12,-[Principal])

-Calculate the balance after the 1st year:
Balance_After_1st_Year: FV([APR_Year_1]/12,12,[Payment_1st_Year],-[Principal])

-Calculate payment for 2nd year:
Payment_2nd_Year:
Pmt([APR_Year_2]/12,([Years]-1)*12,-[Balance_After_1st_Year])

-Calculate the balance after the 2nd year:
Balance_After_2nd_Year:
FV([APR_Year_2]/12,12,[Payment_2nd_Year],-[Balance_After_1st_Year])

-Calculate payment for 3rd year:
Payment_3rd_Year:
Pmt([APR_Year_3]/12,([Years]-2)*12,-[Balance_After_2nd_Year])



--
jl5000
<a href="http://www.joshdev.com"></a>


zyus said:
How to calculate monthly repayment in Access for this following mortgage loan
on a multi tier rates environment (calculated on monthly basis)

Principle Amt - 100,000
Tenure - 25 years
Interest rates 1st year - 1.50%
2nd year - 5.00%
3rd year - 6.25%

Monthly repayment 1st year - ?
2nd year - ?
3rd year - ?

Appreciate your response.. ?
Click to expand...
Click to expand...
 
Back
Top