[R] How to do a backward calculation for each record in a dataset

Rolf Turner rolf.turner at xtra.co.nz
Mon Feb 18 21:25:03 CET 2013

Some (quite a few!) years ago I wrote myself a wee function called
compInt() ("compound interest") to do --- I think --- just what you require.
I have attached the code for this function and a help file for it.

If anyone else wants this code, and if the attachments don't get through 
the list,
let me know and I can send the stuff to you directly.


         Rolf Turner

On 02/18/2013 10:34 PM, Prakasit Singkateera wrote:
> Hi all,
> Firstly, it is not a homework. I am working for a hotel booking company in
> Thailand but I don't want to explain a complex equation and concept here so
> I keep it simple and closely related to what I am trying to solve.I apology
> if my question is not clear enough.
> I am new to R and previously this problem can be solved easily in Excel
> using the "Goal Seek" tool. An example related to my question is when we
> use the PMT formula (in Excel) to find the loan payment amount for the
> given values of parameters i.e. interest rate, total number of payments,
> and principal amount of the loan.
> loan_payment_amt_of_each_period =
> PMT(interest_rate,total_number_of_payments,principal_amt)
> The question is when you know exactly on a monthly basis that you can
> afford only X amount of money to pay and you want to know how many months
> you have to do the payment given your monthly affordable money, the fixed
> interest rate, and the principal amount of loan. Using Goal Seek tool in
> Excel, it is like a backward solving for X given Y by not having to
> transform anything from the original equation. Simply put the
> loan_payment_amt_of_each_period you want and let the software calculate the
> total_number_of_payments for you.
> Thanks arun. But that was you solved the original equation and put it as a
> new formula to R to calculate the result which is easy as long as the
> original equation is not complex.
> Thanks you,
> Prakasit Singkateera
> On Mon, Feb 18, 2013 at 1:18 AM, Bert Gunter <gunter.berton at gene.com> wrote:
>> Homework? We don't do homework here.
>> -- Bert
>> On Sun, Feb 17, 2013 at 5:10 AM, Prakasit Singkateera
>> <asltjoey.rsoft at gmail.com> wrote:
>>> Hi Experts,
>>> I have a dataset of 3 columns:
>>> customer.name     product     cost
>>> John     Toothpaste     30
>>> Mike     Toothpaste     45
>>> Peter     Toothpaste     40
>>> And I have a function of cost whereby
>>> cost = 3.40 + (1.20 * no.of.orders^2)
>>> I want to do a backward calculation for each records (each customer) to
>>> find his no.of.orders and create a new column named "no.of.orders" in
>> that
>>> dataset but I don't know how to do.
>>> Please help me.
>>> Thank you everyone,
>>> Prakasit
>> --
>> Bert Gunter
>> Genentech Nonclinical Biostatistics
>> Internal Contact Info:
>> Phone: 467-7374
>> Website:
>> http://pharmadevelopment.roche.com/index/pdb/pdb-functional-groups/pdb-biostatistics/pdb-ncb-home.htm
-------------- next part --------------
compInt <- function(P=NULL,r=NULL,n=NULL,a=NULL) {
# Function compInt.  To calculate one of the parameters P,r,n,a,
# associated with the compound interest formula,
#                     12a
#  (1 + r/12)^n = ------------
#                  (12a - rP)
# given the other three.
# P = principle, r = annual interest rate (compounded monthly),
# n = number of months until loan is paid off; a = monthly payment.

chk <- sum(c(is.null(P),is.null(r),is.null(n),is.null(a)))
if(chk > 1) stop("Must specify either ONE or ZERO non-null arguments.\n")

if(!is.null(P) && (!is.numeric(P) || length(P) != 1 || P <= 0))
	stop("Argument \"P\" must be a positive numeric scalar.\n")
if(!is.null(r) && (!is.numeric(r) || length(r) != 1 || r <= 0))
	stop("Argument \"r\" must be a positive numeric scalar.\n")
if(!is.null(n) && (!is.numeric(n) || length(n) != 1 || n <= 0 ||
	stop("Argument \"n\" must be a positive integer scalar.\n")
if(!is.null(a) && (!is.numeric(a) || length(a) != 1 || a <= 0))
	stop("Argument \"a\" must be a positive numeric scalar.\n")

if(chk==0) {
	A <- ((1+r/12)^n)*(P - 12*a/r) + 12*a/r
	A <- max(A,0)
	if(isTRUE(all.equal(A,0))) {
		nlast <- ceiling(Recall(P=P,r=r,a=a))
		attributes(nlast) <- NULL
	} else nlast <- NULL
	A <- c(A=A)
	if(!is.null(nlast)) attr(A,"lastNonZero") <- nlast

	return(c(P=(12*a/r)*(1 - (1+r/12)^(-n))))

if(is.null(r)) {
	if(P/a > n) stop("You would need a negative interest rate!\n")
	if(n==1) return(c(r=12*(a-P)/P))
	fff <- function(r,P,n,a) {
		fval <- n*log(1+r/12) + log(12*a-r*P) - log(12*a)
		J    <- n/(12+r) - P/(12*a - r*P)
	r1 <- 12*(1+n/P)/(n-1)
	r2 <- 0.99*12*a/P
	rr <- seq(r1,r2,length=100)
	ss <- fff(rr,P,n,a)$fval
	r0 <- rr[which.min(abs(ss))]

if(is.null(n)) {
	if(r*P >= 12*a) return(Inf)
	n <- (log(12*a) - log(12*a - r*P))/log(1+r/12)
	nl <- floor(n)
	A <- Recall(P,r,nl,a)
	n <- c(n=ceiling(n))
	attr(n,"lastPayment") <- unname(A)

	return(c(a=r*P/(12*(1 - (1+r/12)^(-n)))))
-------------- next part --------------
Compound Interest
Calculate one of the parameters \code{P}, \code{r}, \code{n},
\code{a}, associated with the compound interest formula, i.e.:
\deqn{(1 + r/12)^n = \frac{12a}{12a -rP}}{(1+r/12)^n = 12a/(12a -rP)}
given the other three. Alternatively calculate the remaining amount
owing, given all four parameters.  In the compound interest formula
\eqn{P} = principle, \eqn{a} = annual interest rate (compounded
monthly), \eqn{n} = number of months until loan is paid off and
\eqn{a} = monthly payment.
compInt(P = NULL, r = NULL, n = NULL, a = NULL)
  Positive numeric scalar equal to the principle of the loan.
  Positive numeric scalar equal to the annual interest rate (given as a
  \emph{fraction} and NOT as a percentage), compounded monthly.
  Positive integer scalar equal to the number of months until the loan
  is paid off.
  Positive numeric scalar equal to the amount of the monthly payment.
  Either three or four of the four arguments must be specified.  If one
  argument is left unspecified (i.e. left \code{NULL}) then its value
  will be calculated by the function.  If the unspecified argument is \code{n}
  then the returned value has an attribute \code{lastPayment} giving the
  amount of the last payment (which is in general less than \code{a}).

  If all four arguments are specified then the function calculates
  the amount \code{A} remaining to be paid off after \code{n} payments
  have been made.  If \code{A} is zero then the returned value
  has an attribute \code{lastNonZero} which is the payment number
  corresponding to the last non-zero payment.
  A numeric scalar equal to the value of the argument which was
  left \code{NULL}, or if no argument was left \code{NULL}, a numeric
  scalar equal to the amount remaining to be paid off after \code{n}
  payments have been made.  (See \bold{Details}.)
\author{Rolf Turner
  \email{r.turner at auckland.ac.nz}
  The formula was related to me by Ron Sandland, way back in the good
  old days when I was working for D.M.S. Sydney.  I originally coded
  it up in Splus.  Just now (29/October/2011) I dug around in stored
  files, turned up the code, and turned it into an R function.

  The interest rate \code{r} is interpreted as a \emph{fraction}
  and NOT as a percentage.  E.g. if you are thinking of an interest
  rate of 15\% per annum, then \code{r} should be entered as 0.15.

  The monetary values \code{P}, \code{a}, and \code{A}
  returned by the function are \emph{NOT} rounded to the nearest
  \dQuote{cent}, but rather are left with their usual floating
  point representation.

\keyword{ utilities }

More information about the R-help mailing list