CUMPRINC function help needed.

proficient

Well-known Member
Joined
Apr 10, 2012
Messages
745
Office Version
  1. 2016
Platform
  1. Windows
  2. Mobile
The annual rate of interest is 9 %. The period of the loan is 5 years. The present value is
200,000 Rs. Calculate using CUMPRINC Excel function total interest paid in the second
Year of payments, for periods 10 through 18.What is the principal paid in a single
payment in the first month?

Please help to solve this...
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
Did you copy and paste that question from your homework / coursework assignment?
 
Upvote 0
Yes, I have exams in next week, I may be skipped some lectures, and now I need to study on my own to understand this function, but unfortunately I am not able to understand what exactly they are asking to do. :(
 
Upvote 0
You are "missing" a critical piece of information, namely: the frequency of payment. However, that is implied in the last word: "monthly".

In that case, the parameters must be in units of months. So "rate" is typically 9%/12, and "nper" is 5*12.

(Not to over-complicate things, but monthly "rate" is sometimes calculated differently in some countries for some types of loans, notably mortgages. Let us know if your results do not match the answer that might be given in your text. In that case, let us know what answer is given so that we reverse-engineer the monthly rate calculation.)

Also note that CUMPRINC differs from most Excel financial functions insofar as the "pv" parameter __must_be__ positive. Consequently, CUMPRINC returns a negative value. If you want it to be positive (typically), simply write =-CUMPRINC(...). Note the unary minus in front.

For most loans, "type" is zero (payment at the end of the period).

-----

It is odd that the question asks you to use CUMPRINC per se to calculate __interest__. Usually, we would use CUMIPMT.

If you are sure that the question requires that you use CUMPRINC [sic], you will need to calculate the monthly payment using the PMT function.

To make that simple, you can use the same parameters for "rate", "nper" and "pv". But again, note that PMT will return a negative value. The "fv" and "type" parameters can be omitted; or you can enter zero.

Mathematically, the total interest paid in "n" periods is n*payment - cumPrincipal, treating each value as positive. Taking into account that PMT and CUMPRINC return negative values, we would write:

=-(18-10+1)*PMT(...) + CUMPRINC(...)

Of course, you can substitute 9 for (18-10+1). But typically, 10 and 18 are replaced with cell references so that they can be variable.

----

Odd that the question says ``paid in the second Year [sic] of payments, for periods 10 through 18``.

A year being 12 months, usually periods #10-12 are in the __first__ "year" (but not necessarily calendar year) and periods #13-18 are in the __second__ "year".

And the total of periods #10-18 comprise 9 months, not a whole(?) "year" (12 months) or even the number of payments in the second calendar(?) year.

Double-check your reading of the question for any possible misunderstandings.
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,237
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top