Principle reduction question

I know how do use the pmt() function to calculate mortgage payments but
I need to know the easiest way to calculate the total principle
reduction/remaining balance after a set number of payments without doing
an amortization table.

For example: I finance $500,000 for 30 years at an anual interest rate
of 6% making monthly payments. After 3 years (36 payments) I sell the
asset for $650,000 and want to pay off the remaining principle balance
of the loan. How do I calculate that amount in a one cell formula?
Thanks for the help!!!

I believe this will give you your balance:
=500000+Cumprinc(6%/12,30*12,500000,1,36,0)

HTH
--
Dana DeLouis
Win XP & Office 2003


"snuka" <snuka@toke.com> wrote in message
news:VEJWe.11201$Wd7.5818@newsread1.news.pas.earthlink.net...
>I know how do use the pmt() function to calculate mortgage payments but I
>need to know the easiest way to calculate the total principle
>reduction/remaining balance after a set number of payments without doing an
>amortization table.
>
> For example: I finance $500,000 for 30 years at an anual interest rate of
> 6% making monthly payments. After 3 years (36 payments) I sell the asset
> for $650,000 and want to pay off the remaining principle balance of the
> loan. How do I calculate that amount in a one cell formula? Thanks for
> the help!!!

"Dana DeLouis" <delouis@bellsouth.net> wrote in message
news:uVCk%23VyuFHA.3528@TK2MSFTNGP15.phx.gbl...
>I believe this will give you your balance:
> =500000+Cumprinc(6%/12,30*12,500000,1,36,0)

Yes, if 6% is an annual nominal rate of interest corresponding
to an effective monthly interest of 0.5%.

But if 6% is an effective annual rate of interest you must consider
that the monthly rate is from:
(1+i12)^12 = (1+0.06)
i12 = 1.06^(1/12) -1 = 0.004867551
Then:
=500000+CUMPRINC(0.004867551,30*12,500000,1,36,0)
that is 479 865.46 against your 480 420.35, not a big difference.

Do you agree?

Ciao
Bruno

Hi. Not sure, so to check, I went to Excel, and did File | New | and opened
the "Loan Amortization" template.
I filled in the data, and checked the balance after 36 months. Excel's loan
template also shows
$480,420.35 at the end of 36 payments.

HTH ;>0
--
Dana DeLouis
Win XP & Office 2003


"Bruno Campanini" <bruno.campanini@tin.it> wrote in message
news:%23MVGUryuFHA.2540@TK2MSFTNGP09.phx.gbl...
> "Dana DeLouis" <delouis@bellsouth.net> wrote in message
> news:uVCk%23VyuFHA.3528@TK2MSFTNGP15.phx.gbl...
>>I believe this will give you your balance:
>> =500000+Cumprinc(6%/12,30*12,500000,1,36,0)
>
> Yes, if 6% is an annual nominal rate of interest corresponding
> to an effective monthly interest of 0.5%.
>
> But if 6% is an effective annual rate of interest you must consider
> that the monthly rate is from:
> (1+i12)^12 = (1+0.06)
> i12 = 1.06^(1/12) -1 = 0.004867551
> Then:
> =500000+CUMPRINC(0.004867551,30*12,500000,1,36,0)
> that is 479 865.46 against your 480 420.35, not a big difference.
>
> Do you agree?
>
> Ciao
> Bruno
>

"Dana DeLouis" <delouis@bellsouth.net> wrote in message
news:%234zjg5yuFHA.128@TK2MSFTNGP09.phx.gbl...
> Hi. Not sure, so to check, I went to Excel, and did File | New | and
> opened the "Loan Amortization" template.
> I filled in the data, and checked the balance after 36 months. Excel's
> loan template also shows
> $480,420.35 at the end of 36 payments.

That's why Excel always assumes monthly interest rate to
be [(annual interest rate) / 12], and similarly for bimestrial,
quarterly ones, etc.
But this is only an approximation and it is
not mathematically correct.

Bruno

Thanks!
I just needed to load the analysis toolpak.
Is there a mathematical way to accoplish the same thing in case I send
this file to somebody that can not load the tookpak?

Bruno Campanini wrote:
> "Dana DeLouis" <delouis@bellsouth.net> wrote in message
> news:%234zjg5yuFHA.128@TK2MSFTNGP09.phx.gbl...
>
>>Hi. Not sure, so to check, I went to Excel, and did File | New | and
>>opened the "Loan Amortization" template.
>>I filled in the data, and checked the balance after 36 months. Excel's
>>loan template also shows
>>$480,420.35 at the end of 36 payments.
>
>
> That's why Excel always assumes monthly interest rate to
> be [(annual interest rate) / 12], and similarly for bimestrial,
> quarterly ones, etc.
> But this is only an approximation and it is
> not mathematically correct.
>
> Bruno
>
>
>

> Is there a mathematical way to accomplish the same thing in case I send
> this file to somebody that can not load the tookpak?

Hi. Since you have the payment...
Balance after 'n' periods...

Balance = ((ir * loan - payment) * (ir + 1) ^ n + payment) / ir

Here's a test with vba...

Sub Demo()
Dim ir
Dim loan

Dim payment
Dim n
Dim Balance

ir = 0.06 / 12
loan = 500000

payment = -Pmt(ir, 360, 500000)
n = 36
Balance = ((ir * loan - payment) * (ir + 1) ^ n + payment) / ir
Debug.Print FormatCurrency(Balance, 2)
End Sub

This returns a balance of $480,420.35 after 36 months
HTH :>)
--
Dana DeLouis
Win XP & Office 2003


"snuka" <snuka@toke.com> wrote in message
news:9kXWe.11341$Wd7.2848@newsread1.news.pas.earthlink.net...
> Thanks!
> I just needed to load the analysis toolpak.
> Is there a mathematical way to accoplish the same thing in case I send
> this file to somebody that can not load the tookpak?
>
> Bruno Campanini wrote:
>> "Dana DeLouis" <delouis@bellsouth.net> wrote in message
>> news:%234zjg5yuFHA.128@TK2MSFTNGP09.phx.gbl...
>>
>>>Hi. Not sure, so to check, I went to Excel, and did File | New | and
>>>opened the "Loan Amortization" template.
>>>I filled in the data, and checked the balance after 36 months. Excel's
>>>loan template also shows
>>>$480,420.35 at the end of 36 payments.
>>
>>
>> That's why Excel always assumes monthly interest rate to
>> be [(annual interest rate) / 12], and similarly for bimestrial,
>> quarterly ones, etc.
>> But this is only an approximation and it is
>> not mathematically correct.
>>
>> Bruno
>>
>>

"snuka" <snuka@toke.com> wrote in message
news:9kXWe.11341$Wd7.2848@newsread1.news.pas.earthlink.net...

> Thanks!
> I just needed to load the analysis toolpak.
> Is there a mathematical way to accoplish the same thing in case I send
> this file to somebody that can not load the tookpak?

Yes, this is the mathematical formula you can simply translate
in Excel, without the need of Analysis Toolpack.

Given:
i = monthly rate of interest (0.004867551 or 0.005)
C = initial amount ($ 500 000)
n = number of monthly payments (360)
t = time at which you want to calculate the ramaining principle (36)

RemainingPrinciple = C*(1-((1+i)^t-1)/((1+i)^n-1))

If you have:
i in A1
C in A2
n in A3
t in A4
Excel formula is:
=A2*(1-((1+A1)^A4-1)/((1+A1)^A3-1))

Bruno