[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.

     cheers,

         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 ||
                   !isTRUE(all.equal(n,round(n)))))
	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(A)
}

if(is.null(P))
	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)
		list(fval=fval,jacobian=J)
	}
	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))]
	return(c(r=newt(fff,start=r0,P=P,n=n,a=a)))
}

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(n)
}

if(is.null(a))
	return(c(a=r*P/(12*(1 - (1+r/12)^(-n)))))
}
-------------- next part --------------
\name{compInt}
\alias{compInt}
\title{
Compound Interest
}
\description{
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.
}
\usage{
compInt(P = NULL, r = NULL, n = NULL, a = NULL)
}
\arguments{
  \item{P}{
  Positive numeric scalar equal to the principle of the loan.
}
  \item{r}{
  Positive numeric scalar equal to the annual interest rate (given as a
  \emph{fraction} and NOT as a percentage), compounded monthly.
}
  \item{n}{
  Positive integer scalar equal to the number of months until the loan
  is paid off.
}
  \item{a}{
  Positive numeric scalar equal to the amount of the monthly payment.
}
}
\details{
  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.
}
\value{
  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}
  \url{http://www.math.unb.ca/~rolf}
}
\note{
  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.
}

\section{Warnings}{
  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.
}

\examples{
compInt(P=800,r=0.15,a=40)
compInt(P=800,r=0.15,n=24)
compInt(P=800,n=24,a=40)
compInt(r=0.15,n=24,a=40)
compInt(P=800,r=0.15,n=24,a=40)
compInt(P=800,r=0.15,n=24,a=30)
}
\keyword{ utilities }


More information about the R-help mailing list