CUMPRINC Function Returns #N/A

  • Thread starter Thread starter George Sullivan
  • Start date Start date
G

George Sullivan

I am having a terrible problem getting a function called
CUMPRINC to work in Excel 2000. I found a document at
Microsoft Tech support called "XL2000:Analysis ToolPak
Function Unexpectedly Return #NAME Error Value" and
followed the instructions in it. Before that I had
installed the Analysis ToolPak per instructions in Excel
Help. The function appears to be recognized because it
capitalizes the name after I enter it in lower case and
press enter.But for the life of me it just returns #N/A. I
have simplified the arguments to the point of entering
them as numbers and not cell references and it still won't
work. I know this is a bit obscure but is there anyone who
has run into this problem? I need the function and cannot
think of a reasonable substitute. Thanks.
 
Have you tried the example from Help?
A2 9% interest rate (annual)
A3 30 term in years
A4 125,000 present value
A5 =CUMPRINC(A2/12,A3*12,13,24,0) returns -934.1071

What values have you tried
 
Hi George!

Without numbers it's difficult to help. So I'll toss in a few of my
own for you to test and compare with what you've done.

Try the following:

A1: Loan B1: 100000
A2: Term B2: 120
A3: Rate B3: 0.50%
A4: Type B4 0

A6 StartPer B6 24
A7: EndPer B7: 36

A10:
=CUMPRINC(B3,B2,B1,B6,B7,B4)
Returns: -9168.75862183883

With a negative sign, this is interpreted as the amount of principal
repaid between payments 24 and 36 of the loan.

CUMPRINC is capable of being substituted by:

=FV(B3,B6-1-(B4=1),PMT(B3,B2,B1,B5,B4),B1,B4)-FV(B3,B7-(B4=1),PMT(B3,B2,B1,B5,B4),B1,B4)
Returns: -9168.75862183864

I've not attempted simplification because the formula demonstrates the
concept.

Like me, it's not a pretty sight even before the beer is added. The
principle is one of difference of FVs when you accumulate both loan
and repayments. But implementation requires a close reading of what
CUMPRINC is calculating and how to cover the alternative assumptions
of payments in advance or in arrears.

Anticipating (for good reason) the next question. CUMIPMT is easy now
we have the principal repaid. Just multiply the amount of repayments
by the number of repayments and deduct the principle repaid:

=CUMIPMT(B3,B2,B1,B6,B7,B4)
Returns: -5263.90663057582

And:
=(PMT(B3,B2,B1,B5,B4)*(B7-B6+1))-(FV(B3,B6-1-(B4=1),PMT(B3,B2,B1,B5,B4),B1,B4)-FV(B3,B7-(B4=1),PMT(B3,B2,B1,B5,B4),B1,B4))
Returns: -5263.90663057611

I toss this additional point in because if you look at the formula
you'll see that the number of payments involved is *not* (B7-B6)
=12 but is (B7-B6+1) = 13.

So if using to calculate amounts repaid in interest or principal per
year, don't get tempted to use (eg) 12 and 24 but use 12 and 23.

Maybe, to modify slightly your words, the Analysis ToolPak (or
Alcohol) free version is not a good substitute. However, it
demonstrates the principles involved and if you're looking for a
robust model that will work on different computers...
 
It works with the numbers you gave me! But I am trying to
use this to calculate some credit card payments with
relatively small principal amounts. Specifically I am
trying CUMPRINC(.0175,10.4,360,1,9.8,0). There is a
partial month in there...is that the problem? I can make a
similar function (PPAYMT) work with these numbers in Lotus
123 but not here.
Thanks for getting back.
George
 
I am trying =CUMPRINC(.0175,10.4,360,1,9.8,0). With your
numbers it works. Is the partial month causing the problem?
I am able to make it work with a similar function PPAYMT
in Lotus 123 but not in Excel.
Thanks. George
 
Norman, I started to use the alternate formula you sent
but what is B5? Thanks. George
 
Hi George!

I knew I should have covered that. It's zero! It should be the FV.
Those formulas don't cover balloon mortgages! I haven't checked yet
what ATP does and it was daylight (and I was about to turn to dust).
I'll be working on it later.

I think it's just a case of deducting the balloon from the PV to get
the CUMPRINC and doing the same for CUMIPMT as before plus adding
interest on the balloon for the number of periods.

But it needs checking and I don't like sending out my stuffups
(technical appraisal term!)

Partial periods will be wrong. I much prefer amortization tables for
these things as they can be much more accurate. I've got a new set for
good homes on direct application (but not quite finished on the
annotation). Includes daily compounding of APR mortgages with Variable
rates and Additional payments (plus bells, whistles and fluffy dice).
Ideal for US financial year and even adaptable in principle for
Canadians, Poms and Aussies.
 
Hi!

I've done some checking and there are some problems with those
alternatives to CUMPRINC and CUMIPMT where the user is awkward and
wants to cover period 1.

Anyways the corrections, which seem to work OK are:

Cell references as before but I have replaced with names.

CUMIPMT:
=CUMIPMT(IntRate,Term,Loan,BegPer,EndPer,Basis)
or:
=IF(AND(BegPer=1,Basis=1),(Loan-PMT(B3,B2,B1,0,Basis))+FV(IntRate,EndPer-Basis,PMT(B3,B2,B1,0,Basis),Loan,Basis)+(PMT(B3,B2,B1,0,Basis)*((EndPer-BegPer)+1)),-(FV(IntRate,BegPer-1-Basis,PMT(B3,B2,B1,0,Basis),Loan,Basis)-FV(IntRate,EndPer-Basis,PMT(B3,B2,B1,0,Basis),Loan,Basis)-(PMT(B3,B2,B1,0,Basis)*((EndPer-BegPer)+1))))

CUMPRINC:
=CUMPRINC(IntRate,Term,Loan,BegPer,EndPer,Basis)
or:
=PMT(IntRate,Term,Loan,0,Basis)*(EndPer-BegPer+1)-B14

Both alternatives are almost certainly capable of being made more
efficient.

Neither alternative to ATP covers the balloon mortgage problem for
both payments in arrears and payments in advance. Yet!

I knew there was a reason why I prefer amortization schedules for
these calculations!
 
Hi George!

Re partial periods:

Help for CUMPRINC

Nper, start_period, end_period, and type are truncated to integers.

So that's not on for this function.

I also have conceptual problems with part periods. What does it mean?
Do I ring the bank manager and wave goodbye at 2:00AM one morning part
of the way through the final month when in practice the repayments are
made at the beginning or end or whatever? I practice you make an extra
large payment at INT(Nper) or a smaller payment at INT(Nper)+1

But you are trying to work out problems with Credit Card regimes!
Those calculations are a lot more difficult that those handled by the
financial functions because the payments vary from month to month
depending upon the balance and the minimum repayment rules.

With these problems, you're almost certainly going to need an
amortization schedule of some sort although I think that the following
are OK for working out the total "life" of the loan and the total
amount repaid if you stick to minimum repayments.

Will either of these help? (No promises as to efficiency or otherwise)

Function CardNPer(Loan As Double, LoanRate As Single, RepayPercent As
Double, RepayMinAmount As Single)
Dim SuckerRepayment As Double
Dim Interest As Double
Dim NeverNever As Integer
Do Until Loan >= 0
If RepayPercent * -Loan >= RepayMinAmount Then
SuckerRepayment = RepayPercent * -Loan
Else
If RepayPercent * -Loan > -Loan Then
SuckerRepayment = -Loan + (RepayPercent * -Loan)
Else
SuckerRepayment = RepayMinAmount
End If
End If
Interest = Loan * LoanRate
Loan = Loan + SuckerRepayment + Interest
NeverNever = NeverNever + 1
Loop
CardNPer = NeverNever
End Function


Function CardInterest(Loan As Double, LoanRate As Single, RepayPercent
As Double, RepayMinAmount As Single)
Dim SuckerRepayment As Double
Dim Interest As Double
Dim TotalInterest As Double
Dim NeverNever As Integer
Do Until Loan >= 0
If RepayPercent * -Loan >= RepayMinAmount Then
SuckerRepayment = RepayPercent * -Loan
Else
If RepayPercent * -Loan > -Loan Then
SuckerRepayment = -Loan + (RepayPercent * -Loan)
Else
SuckerRepayment = RepayMinAmount
End If
End If
Interest = Loan * LoanRate
Loan = Loan + SuckerRepayment + Interest
NeverNever = NeverNever + 1
TotalInterest = TotalInterest + Interest
Loop
CardInterest = TotalInterest
End Function
 
Norman, This isn't a balloon. It is a credit card payment
situation (therefore the high interest rate and relatively
low principal). What I am trying to do is build a spread
sheet where I can take a situation where a person has a
number of credit card debts, order them as quickest to pay
off and the rest in ascending sequence. Then I pay off the
first and then take the payment I was making on the first
and add it to the payment on the second and use that
amount to pay down the second debt. When calculating
payoff of the second debt I use CUMPRINC to calculate how
much was applied to the second debt principal while paying
off the first debt (if all this makes sense to you).
Therefore there are partial month payments and relatively
short end periods, etc.
Thanks for hanging with me.
George Sullivan
 
Norman, In this what is B14?
=PMT(IntRate,Term,Loan,0,Basis)*(EndPer-BegPer+1)-B14
Thanks. George
 
Hi George!

Back to your CUMPRINC problem:

Your data:
=CUMPRINC(0.0175,10.4,360,1,9.8,0)
Returns: -321.1251853594

But:
=CUMPRINC(0.0175,10,360,1,9,0)
Returns: -321.1251853594

Notice the similarity! Note as before Help says that those decimals
10.4 and 9.8 are truncated.

You need to check your data if you're getting #N/A which usually
indicates a missing required argument.

I'd use a amortization style approach for the problem that you have
described.

What you'll get is a series of parallel cash flows with Period
numbers, Dates, Payments, Interest, Principal and balances. These can
be automatically generated based upon user input.

Send direct to address bare details of an example of debts, rates, and
basis of suggested initial base repayment and it won't take long to
set it up. Unfortunately, I'll be out of circulation for two days. I
did a similar exercise for a guy working for a charity in the US a
couple of years back where the aim was to re-package a series of card
debts into one loan over a reduced period.

But setting up the matrix is not too difficult with the only "trick"
being to get the IF function that does the switch over when the
balance on the first card is repaid.

But I wouldn't go within a mile of CUMPRINC and CUMIPMT for these
ones. Formulas and functions can be great but there are times when a
straight forward schedule is a lot easier to handle and can yield a
lot more useful data. If you don't like the look and feel, the
schedule can be hidden so that only the results are shown in the form
you are wanting formulas to achieve.
 
Norman, First of all, thanks to your help, I have the
CUMPRINC working! I have no idea what I was leaving out
but your example below helped me get it done. As far as
being exact (the truncation thing), it really doesn't
matter because I am using this in a presentation to make a
point and not in a committed sense as, for example, a
mortgage offer. It takes some fooling around with + and -
signs as I am using this in conjunction with the NPER
function. CUMPRINC is what is creating the PV number for
NPER that I need. Many thanks to your tenacity and to your
offer to create the amortization schedules...that is
really above and beyond! But I won't need them. The real
help is that the CUMPRINC function is now returning a
number and not the cryptic #N/A. Thanks you again.
George Sullivan
 
Hi George!

Always pleased to help.

I've been doing a lot with amortization schedules recently and you
prompted a few thoughts.
 
Back
Top