# [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.
>>>
>>>
>>> Thank you everyone,
>>> Prakasit
>>
>>
>> --
>>
>> Bert Gunter
>> Genentech Nonclinical Biostatistics
>>
>> Internal Contact Info:
>> Phone: 467-7374
>> Website:
>>
-------------- 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 }