Balloon Loan Repayments | Beginning of Period & End of Period

[math-genius-not]

Hi All,

I'm doing a project whereby I want to match excel PMT formula with the long hand formula.

I am using the attached example:

---

Attached formula only gives an answer for payments paid in arrears (in other words, at the end of the period).

Is there a formula I can use that accounts for payments made from the beginning of the period that matches Excel's PMT function?

	Formula Used	Results	When are Payments Made?
Excel PMT Formula	=PMT(0.12/12,36,-11000,5000,0)	$249.29	End of Period
Math Equaltion	11,000 - (5,000/1.01^36) x 0.01/1-(1+.01)^-36	$249.29	End of Period
Excel PMT Formula	=PMT(0.12/12,36,-11000,5000,1)	$246.82	Beginning of Period
Math Equaltion		$246.82	Beginning of Period

Thanks in advance!

 

[tkhunny]

Hi All,

I'm doing a project whereby I want to match excel PMT formula with the long hand formula.

I am using the attached example:

View attachment 10582

---

Attached formula only gives an answer for payments paid in arrears (in other words, at the end of the period).

Is there a formula I can use that accounts for payments made from the beginning of the period that matches Excel's PMT function?

	Formula Used	Results	When are Payments Made?
Excel PMT Formula	=PMT(0.12/12,36,-11000,5000,0)	$249.29	End of Period
Math Equaltion	11,000 - (5,000/1.01^36) x 0.01/1-(1+.01)^-36	$249.29	End of Period
Excel PMT Formula	=PMT(0.12/12,36,-11000,5000,1)	$246.82	Beginning of Period
Math Equaltion		$246.82	Beginning of Period

Thanks in advance!

Have you considered: \(\displaystyle \dfrac{Math\;Equation\;End\;of\;Period}{1.01}\)?

I don't know why that Math Code isn't working. It works in the preview.

Anyway, the difference between EOP and BOP is just one compounding period of interest. Divide by 1.01 and you're there.

 

[JeffM]

I don't know why that Math Code isn't working. It works in the preview.

The first time that the word "equation" is used causes a link to be generated to an article on "equations, but the LaTeX parser cannot process the link. The symptom is a word in blue in the middle of your LaTeX. The work arounds are to (1) not use the word in LaTeX, or (2) to use the word outside of LaTeX before using it inside LaTeX. Here is your code, but notice that I have previously used the word "equation."

\(\displaystyle \dfrac{Math\;Equation\;End\;of\;Period}{1.01}\)

 

[Deleted member 4993]

The first time that the word "equation" is used causes a link to be generated to an article on "equations, but the LaTeX parser cannot process the link. The symptom is a word in blue in the middle of your LaTeX. The work arounds are to (1) not use the word in LaTeX, or (2) to use the word outside of LaTeX before using it inside LaTeX. Here is your code, but notice that I have previously used the word "equation."

\(\displaystyle \dfrac{Math\;Equation\;End\;of\;Period}{1.01}\)

I would write that as a similar (but more general) equation:

\(\displaystyle \dfrac{Math\;Equation\;End\;of\;Period}{1 + r}\)

 

[math-genius-not]

you guys are awesome!

Thanks!

 

[Denis]

Attached formula only gives an answer for payments paid in arrears (in other words, at the end of the period).

Is there a formula I can use that accounts for payments made from the beginning of the period that matches Excel's PMT function?

Yes: present value of annuity due formula; see here:
http://financeformulas.net/Present_Value_of_Annuity_Due.html

Whether it "matches Excel" is irrelevant: YOU make Excel match the formula!

Remember that (as example):
a $1000 loan @ $100 monthly with immediate payment
is same as a $900 loan with 1st payment 1 month later...got that?