If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
[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) |
#2
|
|||
|
|||
[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) |
Thread Tools | |
Display Modes | |
|
|