How to do a backward calculation for each record in a dataset
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 }