[newbie] financial accounting problem
 

I have a bit of a problem calculating a financial formula that gets the
outstanding principal amount as at the end of month.

My understanding of the formula is:
Outstanding Principal Balance as at end of month =
Monthly Payment *
(1 + monthly interest rate) power of (duration of loan in months - number of
monthly payment made so far) /
(monthly interest rate * (1 + monthly interest rate) power of (duration of
loan in months - number of monthly payment made so far))

I have updated this in excel as
=D17*(POWER((1+D18),(D19-D20)-1)/(D18*(POWER(1+D18,(D19-D20))))).

With the following values, I am getting the answer of ‚¬159,726.57, whilst
the book example I am following is getting an answer of ‚¬118,995.34, with the
following variables:
Monthly payment = 1206.93
Monthly interest rate = 0.075 (0.75%)
duration of loan = 360 months
number of monthly payments made so far = 180 months

Is it possible that I am misinterpreting some operator of precedence (e.g.
powerof function should be done before the multiplication or the other way
round)? Any ideas?

Apologies if I have posted this in an inappropriate forum.


Source of reference
1. Construction Accounting and Financial Management (Chapter 16)

[newbie] financial accounting problem
 

Is there a reason not to use the built-in present value function? With 180
payments remaining, it's just =-PV(0.75%,180,1206.93)
(or, using your cell references, =-PV(D18,D19-D20,D17) )

If you can't use PV, then you can calculate it as
=D17/D18*(1-(1/((1+D18)^(D19-D20)))) (note that a^b is equivalent to
power(a,b))

"Jon" wrote:

I have a bit of a problem calculating a financial formula that gets the
outstanding principal amount as at the end of month.

My understanding of the formula is:
Outstanding Principal Balance as at end of month =
Monthly Payment *
(1 + monthly interest rate) power of (duration of loan in months - number of
monthly payment made so far) /
(monthly interest rate * (1 + monthly interest rate) power of (duration of
loan in months - number of monthly payment made so far))

I have updated this in excel as
=D17*(POWER((1+D18),(D19-D20)-1)/(D18*(POWER(1+D18,(D19-D20))))).

With the following values, I am getting the answer of ‚¬159,726.57, whilst
the book example I am following is getting an answer of ‚¬118,995.34, with the
following variables:
Monthly payment = 1206.93
Monthly interest rate = 0.075 (0.75%)
duration of loan = 360 months
number of monthly payments made so far = 180 months

Is it possible that I am misinterpreting some operator of precedence (e.g.
powerof function should be done before the multiplication or the other way
round)? Any ideas?

Apologies if I have posted this in an inappropriate forum.


Source of reference
1. Construction Accounting and Financial Management (Chapter 16)