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

[Berend Hasselman]

On 18-02-2013, at 10:34, Prakasit Singkateera <asltjoey.rsoft at gmail.com> 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.
> 

You can find R versions of the Excel functions here: http://factbased.blogspot.nl/2013/02/some-of-excel-finance-functions-in-r.html
The R code is here: http://pastebin.com/q7tyiEmM
I do not know if these are a correct translation of what's in Excel or Calc.

For you application to find the number of payments, you can use the R function uniroot for solving a single equation with one unknown.
Small example

# R version of Excel PMT function (as in LibreOffice Calc)
pmt <- function(rate, nper, pv, fv=0, type=0) {
  rr <- 1/(1+rate)^nper
  res <- (-pv-fv*rr)*rate/(1-rr)
  return(res/(1+rate*type))
}

# here x is the number of payments
Rpmt <- function(x, xrate, xpmt, xpv) xpmt - pmt(xrate,x,xpv)

irate <- .05
xpmt <- -10
xpv <- 100

# testing
pmt(irate,10,xpv)
pmt(irate,5,xpv)
pmt(irate,20,xpv)
Rpmt(xpv/xpmt/2,xrate=irate, xpmt=xpmt, xpv=xpv)
Rpmt(xpv/xpmt*2,xrate=irate, xpmt=xpmt, xpv=xpv)

# find number of payments
z <- uniroot(Rpmt,lower=1,upper=100, xrate=irate, xpmt=xpmt, xpv=xpv)  
z
#number of payments 
z$root

# check
Rpmt(z$root,xrate=irate, xpmt=xpmt, xpv=xpv)
pmt(irate,z$root,xpv)
 
Should you desire higher accuracy of the solution, use tol=1e-8 in the uniroot() call.

Berend