28.04

2014

Funkcja ,,szukaj wyniku” (”goal seek”) z MS Excel w R

Autor: artur

Wstęp

Pokażę dzisiaj, jak zrobić w R coś, co w Excelu wykonuje się z pomocą funkcji ,,szukaj wyniku” (goal seek). Funkcja ta pozwala na znalezienie wartości komórki takiej, żeby dowolnie skomplikowana formuła zależna od wartości w tej komórce (i być może innych) dawała wynik zadany w innej komórce.

Ta funkcja może posłużyć do znalezienia ceny produktu spełniającej określone warunki, punktu odcięcia dla modelu scoringowego, który daje oczekiwane ryzyko lub wielu innych wielkości.

Po przeczytaniu artykułów ,,Jak żyć z odsetek, czyli od zera do rentiera” Macieja Samcika z Gazety Wyborczej oraz ,,Za 50 zł miesięcznie stwórz milionera, czyli najlepszy prezent na Dzień Dziecka” z bloga tego samego autora, okazało się, że mam dobry przykład do tego wpisu.

Zastanowimy się, ile należy odkładać miesięcznie, żeby przechodząc na emeryturę mieć odłożony równy milion (1.000.000) złotych.

Jak to policzyć ,,na papierze”?

Najpierw policzmy, ile zysku da kwota $V_0$ zainwestowana na pewien okres. Skorzystamy ze wzoru na procent składany.

Zakładając dla uproszczenia roczną kapitalizację, składając kwotę $V_0$ na procent $r$ po $n$ latach mamy: $V = V_0 \cdot (1 + r) ^ n$

Oznacza to, że oszczędzając każdego roku kwotę $V_d$ mamy po n latach: $V = \sum_{i=n}^1 V_d \cdot (1 + r) ^ i$

Analogicznie będzie dla kapitalizacji miesięcznej, ale wtedy używamy stopy procentowej $r / 12$ i kwoty $V_d / 12$.

Bardziej uważny czytelnik zauważy, że kwotę $V_0$ można wyciągnąć przed sumę i zadanie można rozwiązać analitycznie (tzn. na kartce).

Na pewne pytania jednak jest trochę trudniej odpowiedzieć z wykorzystaniem obliczeń na papierze, na przykład: jak długo powinniśmy odkładać po 100 zł, żeby mieć na koncie milion złotych? Tutaj trzeba użyć wzoru na sumę wyrazów ciągu geometrycznego i rozwiązać proste równanie.

Są jednak dwa przykłady, kiedy zadanie jest trudniejsze i nie da się go rozwiązać analitycznie:

  • Kiedy zbierzemy milion, jeśli odkładana kwota będzie zmienna, na przykład zależnie od przewidywanej pensji w różnych okresach życia?
  • Jakie powinno być oprocentowanie, żeby odkładając miesięcznie daną kwotę uzyskać kwotę, której pragniemy?

Tymi przykładami zajmiemy się później.

Jak to policzyć w Excelu?

Zakładam, że czytelnik już to wie i szuka innych możliwości. Ale jeśli nie, to zapraszam do przeczytania krótkiego tutoriala.

Jak to policzyć w R?

Policzmy, ile byśmy zaoszczędzili, gdybyśmy zaczęli odkładać w wieku 30 lat po 100 zł miesięcznie aż do emerytury (czyli przez 35 lat) i zyskując przeciętnie rocznie 7%.

Zacznijmy od napisania odpowiedniej funkcji w R:

kwota.oszczednosci <- function(kwota, procent, okres) {
    return(sum(kwota * ((1 + procent)^(okres:1))))
}

Wywołujemy ją w taki sposób:

# kapitalizacja roczna
kwota.oszczednosci(kwota = 1200, procent = 0.07, okres = 35)
## [1] 177496
# kapitalizacja miesięczna
kwota.oszczednosci(kwota = 100, procent = 0.07/12, okres = 35 * 12)
## [1] 181156

Obliczenia można zweryfikować z wykorzystaniem jednego z dostępnych w Sieci kalkulatorów.

Jak zostać milionerem?

Teraz zastanówmy się, ile trzeba odkładać miesięcznie, zaczynając w wieku 30 lat, żeby idąc na zasłużoną emeryturę mieć na koncie okrągły milion złotych. Spróbujmy zgadnąć korzystając z naszej funkcji:

kwota.oszczednosci(kwota = 500, procent = 0.07/12, okres = 35 * 12)
## [1] 905780
kwota.oszczednosci(kwota = 550, procent = 0.07/12, okres = 35 * 12)
## [1] 996358
kwota.oszczednosci(kwota = 600, procent = 0.07/12, okres = 35 * 12)
## [1] 1086936

Prawie trafiliśmy. Jeśli chcemy mieć zgrubne szacowanie, to taka metoda prób i błędów jest wystarczająca. Ale co zrobić, jeśli chcemy wiedzieć dokładnie?

Porządne rozwiązanie w R

Tak naprawdę, to interesuje nas rozwiązanie równania $kwota.oszczednosci(kwota, procent = 0.07 / 12 , okres = 35 * 12) = 1.000.000$ z niewiadomą $kwota$. Zmienne $procent$$okres$ są ustalone i traktujemy je jako parametry.

Popatrzmy, czego szukamy. Oto wykres zależności kwoty oszczędności od kwoty wpłaty:

kwota.wektor <- seq(0, 1500, 100)
kwota.oszczednosci.wektor <- numeric(length(kwota.wektor))
 
for (i in seq_along(kwota.wektor)) {
    kwota.oszczednosci.wektor[i] <- kwota.oszczednosci(kwota.wektor[i], procent = 0.07/12, 
        okres = 35 * 12)
}
 
plot(kwota.wektor, kwota.oszczednosci.wektor, xlab = "kwota odkładana miesięcznie", 
    ylab = "kwota zebrana", type = "l", lwd = 2)
abline(h = 1e+06, col = "red")
Zależność kwoty oszczędności od kwoty miesięcznej wpłaty

Zależność kwoty oszczędności od kwoty miesięcznej wpłaty

Przygotujmy teraz nową funkcję:

kwota.oszczednosci.1e6 <- function(kwota, procent, okres) {
    return(kwota.oszczednosci(kwota, procent, okres) - 1e+06)
}

Zamiast szukać kwoty dającej milion jako wynik działania funkcji kwota.oszczednosci, sprawdzamy, kiedy nowa funkcja kwota.oszczednosci.1e6 daje wartość 0. Wykorzystamy funkcję R uniroot, która szuka miejsca zerowego przekazanej do niej dowolnej funkcji:

kwota <- uniroot(kwota.oszczednosci.1e6, procent = 0.07/12, okres = 35 * 12, 
    interval = c(0, 1000))
print(kwota)
## $root
## [1] 552
## 
## $f.root
## [1] -1.164e-10
## 
## $iter
## [1] 2
## 
## $estim.prec
## [1] 6.104e-05
kwota$root  # tutaj mamy wynik
## [1] 552

Widzimy, że odkładana miesięcznie kwota $552$ zł daje nam ponad milion złotych.

Zwróćmy uwagę, że:

  • Do funkcji uniroot potrzebujemy podać przedział (parametr interval) mówiący o obszarze, w którym poszukujemy rozwiązania.
  • Parametry procentokres przekazujemy do funkcji kwota.oszczednosci.1e6 poprzez funkcję uniroot. Takich parametrów może być dowolnie dużo.

Wracając do wykresu, widać, że zależność jest liniowa. Potwierdza to naszą obserwację, że poszukiwaną kwotę można znaleźć rozwiązując bardzo proste równanie.

A co się stanie, jeśli będziemy zarabiać i odkładać coraz więcej?

Tak, jak pisałem, jeśli odkładana kwota będzie zmienna, na przykład zależnie od przewidywanej pensji w różnych okresach życia (pewna jej część), to sytuacja trochę się komplikuje. Ale nadal takie zadanie można rozwiązać w R.

Potrzebujemy mieć funkcję, która podaje nam dochody zależnie od wieku. Na przykład zakładając 5% rocznego wzrostu wynagrodzenia od dochodu początkowego 3000 zł w wieku 30 lat:

dochody.wiek <- function(wiek, dochody.poczatkowe.30) {
    # przekazujemy możliwie mało parametrów współczynnik wzrostu w stosunku
    # do dochodu początkowego
    wspolczynnik <- function(wiek) {
        return((1 + 0.05)^(wiek - 30))
    }
 
    return(dochody.poczatkowe.30 * ifelse(wiek < 30, 1, ifelse(wiek > 65, wspolczynnik(65), 
        wspolczynnik(wiek))))
}

Popatrzmy, jak rosną dochody:

plot(30:65, dochody.wiek(30:65, 3000), xlab = "wiek", ylab = "dochody", type = "l", 
    lwd = 2)
Ilustracja wzrostu dochodów

Ilustracja wzrostu dochodów

Oczywiście, wzrost dochodów może być opisany bardziej skomplikowaną funkcją.

Teraz napiszemy nową funkcję liczącą oszczędności, która uwzględnia zmianę dochodu i odkładanej kwoty w czasie, przy założeniu, że zawsze pewien procent dochodów, wykorzystując wcześniej napisaną funkcję:

kwota.oszczednosci.zmienne.dochody <- function(procent.dochodow, procent, okres, 
    dochody.poczatkowe.30) {
    return(sum(dochody.wiek(30 + 0:(okres - 1), dochody.poczatkowe.30) * procent.dochodow * 
        ((1 + procent)^(okres:1))))
}

Potrzebujemy kolejnej funkcji:

kwota.oszczednosci.zmienne.dochody.1e6 <- function(procent.dochodow, procent, 
    okres, dochody.poczatkowe.30) {
    return(kwota.oszczednosci.zmienne.dochody(procent.dochodow, procent, okres, 
        dochody.poczatkowe.30) - 1e+06)
}

Podobnie, jak poprzednio, szukamy rozwiązania z pomocą funkcji uniroot:

uniroot(kwota.oszczednosci.zmienne.dochody.1e6, procent = 0.07/12, okres = 35 * 
    12, dochody.poczatkowe.30 = 3000, interval = c(0, 1))$root
## [1] 0.03748

W naszej sytuacji odkładając nieco mniej niż 4% dochodów, zrealizujemy nasz cel finansowy.

Jaki należy mieć zwrot z inwestycji, żeby wcześniej osiągnąć milion?

Załóżmy teraz, że zarobki są bez zmian, ale chcemy mieć milion w wieku 55 lat i dodatkowych 10 lat na korzystanie z niego. Jak dobre możliwości inwestowania pieniędzy musimy znaleźć?

To zadanie zostawiam czytelnikowi. Teraz masz wszystkie narzędzia potrzebne, aby to policzyć :-)

Kiedy jest sens liczyć w R, a nie w Excelu

Tradycyjnie już, zastanówmy się, kiedy warto skorzystać z R zamiast Excela:

  • nie chcemy za każdym razem wyklikiwać funkcji ,,szukaj wyniku” i chcemy mieć ją gotową do wykorzystania,
  • kiedy stosujemy bardziej skomplikowane funkcje,
  • gdy potrzebujemy większej szybkości obliczeń,
  • jeżeli chcemy wykonać więcej takich operacji.

Uwagi

  • Cały model jest bardzo uproszczony.
  • Zakładamy brak podatku Belki.
  • Różnice w wynikach z kalkulatora i R wynikają z różnych sposobów kapitalizacji.
  • Różnice między Excelem i R wynikają z wykorzystania różnych metod optymalizacji.
  • Rozwiązania nie wszystkich problemów da się uzyskać z wykorzystaniem funkcji uniroot. Wtedy warto poszukać innych metod.

Spróbuj ponownie