CUMPRINC function - need help
Guy Ableman
but it has to incorporate a future value. The PPMT function has this option
but using the sum of twelve of these functions for a years principal is a
too long. Does anybody know of a way, or the mathematical formula, for
calculating this.
Thanks in advance,
Guy
Norman Harker
Short answer:
=CUMPRINC(Rate,NPer,PV-FV,Start_Per,End_Per,Type)
But let's work this through as it's not obvious: why else would you ask?
You have a loan, presumably with a balloon element as you want your
calculation to find cumulative principal repaid and incorporate an FV.
I presume that you know:
Original loan amount: say $100,000
Original loan term say 10 years
Interest rate per period of loan :payment say 8% nominal compounded monthly
Balloon amount say $40,000
From this we can calculate the amount of principal repaid at the end of a
year using:
=CUMPRINC(8%/12,120,100000-40000,1,12,0)
returns -4083.147036
Let's check this logic:
Payments are:
=PMT(8%/12,120,100000,-40000,0)
returns -994.63
If we accumulate these payments and the debt for 12 months
=FV(8%/12,12,-994.63,100000,0)
returns -95,916.88
Since the original debt was $100,000
100000-95916.88=4,083.12 (error 0.027 because of rounding)
hth
"Guy Ableman" <g...@breathe.com> wrote in message
news:KXC17.104$bU3.3586@news1-hme0...
Norman Harker
As well as the use of CUMIPMT we can use a general principle in calculating
loan repayments:
Loan at start - Loan at end = Principal repaid
Loan at end =
Accumulated initial balance
- Accumulated repayments
Principal repaid =
Loan at Start
-Accumulated Loan
+Accumulated Payments
So with loan of $100000 over 10 years at 10%/12 per month.
Loan at start = 100000
Accumulated Loan = 100000*(1+10%/12)^12 = 110471.3067
Payments
=PMT(10%/12,120,100000,0,0) = -1,321.51
Accumulated payments:
=FV(10%/12,12,-1321.51,0,0) = 16,605.52
Repaid= 100000-110471.31+16605.52) =6134.21
=CUMPRINC(10%/12,120,100000,1,12,0)
returns 6134.184
This principle can be adapted for balloon loans. The original loan is
accumulated as before. The payments are calculated incorporating the balloon
element as a negative FV.
But we saw before that we can cut out the calcs using CUMPRINC and adjusting
the PV by deducting the balloon element.
hth
"Guy Ableman" <g...@breathe.com> wrote in message
news:KXC17.104$bU3.3586@news1-hme0...
Guy Ableman
Guy.
"Norman Harker" <nha...@ozemail.com.au> wrote in message
news:_SD17.88123$Rr4....@ozemail.com.au...
Jay T. Emory
It looks like you've got this one nailed down pretty
thoroughly. But in looking into the PPMT function, mostly
because I never have before, I stumbled on a real curious
scenario (somewhat related to the topic).
I enter this formula in A1:A12
=PPMT(10%/12, ROW(), 24, 2000)
Then total all 12 to get ($950.25)
Next I array-enter the following in another cell somewhere:
{=SUM(PPMT(10%/12,(IF(C1:C12="",ROW())),24,2000))} ,
where C1:C12 are blank, to get ($907.48)!!!
Something funny is going on here. Any ideas?
Thanks,
Jay
>.
>
Norman Harker
Your first answer is the right one:
=CUMPRINC(10%/12,24,2000,1,12,0)
Returns -950.2482932
The array approach using PPMT applied to the same data should be:
{=SUM(PPMT(10%/12,ROW(A1:A12),24,2000))}
Entered using Ctrl+Shift+Enter
Which, by a miracle of coincidence (plus a teaspoon of luck)
returns -950.2482932
A different and interesting approach showing yet again the power of the
single cell array formula. What's particularly powerful is that the contents
of A1:A12 are irrelevant. We only love them for their bodies! Sorry! I meant
row numbers!
Sees Ya!
"Jay T. Emory" <jte...@aol.com> wrote in message
news:359a01c106eb$749f4240$19ef2ecf@tkmsftngxa01...
Jay T. Emory
I fear you will wish me to go away but I had to thank you
for the reply and clarification. I knew there had to be a
way to use the row numbers in that formula, just strayed
off track a bit because my answers were so close. I think
I've straightened myself out.
My "IF(C1:C12="",Row())" portion was giving me an array of
twelve "ones" whereas your "Row(A1:A12)" portion yields
an array of twelve values from 1 to 12. Thats what I was
trying to get to.
As usual, many thanks for your help. Please bear with me
on the following as I'm engineer with too much curiousity
and too little skill and you may be tiring of my rambling.
I'm not sure using the array formula with the principle
payment function is giving the correct answer. If I apply
the data in your example to Guy, I get ($9,527.34).
{=SUM(PPMT(8%/12,ROW(A1:A12), 120,100000,40000,0))}
Did I interpret the variables wrong? Perhaps the sum of a
series of PPMT is not supposed to equal CUMPRINC? Your
solution for Guy made perfect sense to me. I hope I'm not
beating a dead function.
Again, thanks for your help.
Jay
>.
>
Norman Harker
You'll not annoy me that easily: I've had 20 years of students trying to
wind me up and none have succeeded yet!
Two possible array solutions both produce the right answer:
Closest to yours is:
=SUM(PPMT(8%/12,ROW(A1:A12), 120,100000,-40000,0))
You got the sign of the FV wrong. If persona of borrower is adopted, the
loan comes towards him/her and the repayment of the balloon goes away from
him/her. Your formula treated the borrower as receiving a lump sum
accumulation after the end of the term.
But we know that principal repayments are only made on the non-balloon
element. So we can use the same principle as in the CUMIPRINC function and
use PV-FV for the PV argument:
This gives array formula:
=SUM(PPMT(8%/12,ROW(A1:A12),120,60000))
Now what was bugging me is the answer you got with your original array as
there was clearly some meaning to it. Your deduction that it was returning
12 'ones' sets that to rest because your figure is 12*principal repayment in
the first month.
But my preference goes to the CUMPRINC solution. The only limitation to the
CUMPRINC would be where the product not only repays the loan but also builds
up a positive balance at the end. (In UK they have such products called
endowment mortgages). In that case you'd need to adjust the PV by the
present value of the balance at the end; not a pretty sight first thing on a
Sunday!
Hope this clears up some problems and causes more!
Sees Ya
"Jay T. Emory" <jte...@aol.com> wrote in message
news:1d9b01c10707$306893d0$a5e62ecf@tkmsftngxa07...
Jay T. Emory
I see the logic in the -$40,0000 future value, at least to
the extent it makes sense to accountants. How about I
stick to engineering and you keep world finances in
order? This discussion has proven very engaging and
educational for me. Your wisdom is awesome!
Thanks, as many times before,
>.
>
Norman Harker
There are two main keys to playing with Excel Financial Functions:
1. Make sure you get the signs right. Best approach is to adopt a persona
and determine whether any flow is away from them (-) or towards them.
2. Ensure that the Rate, NPer, and PMT are input or interpreted using the
same time periods. This often requires a conversion of the interest rates
and for this Excel Functions are particularly unhelpful and you must
invariable resort to formula (Unless you use a set of UDFs that I've written
and which will be published shortly).
Glad you enjoyed it but as to my being responsible for World finances? I am
to World Finance what Rudolph Nureyev was to arc welding.
Sees Ya!
"Jay T. Emory" <jte...@aol.com> wrote in message
news:412b01c10749$1ab410e0$b1e62ecf@tkmsftngxa04...